[root@chouyu ~ ]# systemctl start mariadb [root@chouyu ~]# mysql -uroot - p MariaDB [(none)] > show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+
//创建同步账号
MariaDB [(none)]> grant replication slave on *.* to ‘xixi‘@‘192.168.220.20‘ identified by ‘xixi123‘ ;
Query OK, 0 rows affected (0.000 sec)
[root@chouyu ~]# mysql -uroot - p MariaDB [(none)] > show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set ( 0.000 sec) //登录测试 [root@chouyu ~]# mysql -uxixi -pxixi123 -h192. 168.220 . 10 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3 . 11 - MariaDB MariaDB Server Copyright (c) 2000 , 2018 , Oracle, MariaDB Corporation Ab and others. Type ‘ help; ‘ or ‘ \h ‘ for help. Type ‘ \c ‘ to clear the current input statement.
#配置主库数据库 [root@chouyu ~]# vim /etc/ my.cnf ... [mysqld] server - id = 10 # 添加启用binlog日志 log -bin = mysql_bin #添加据库服务器唯一标识符，主库的server- id值必须比从库的小 #重启mysql [root@localhost ~ ]# service mysqld start Starting MySQL. SUCCESS ! MariaDB [(none)] > show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql_bin. 000001 | 328 | | | +------------------+----------+--------------+------------------+ 1 row in set ( 0.000 sec) MariaDB [(none)] >
// 配置主从
[root@chouyu ~]# vim /etc/ my.cnf
...
[mysqld]
server - id = 20
relay -log = mysql_relay_bin
[root@chouyu ~ ]# systemctl restart mariadb
// 配置从数据库
MariaDB [(none)]> change master to
-> master_host= ‘ 192.168.220.10 ‘ ,
-> master_user= ‘ xixi ‘ ,
-> master_password= ‘ xixi123 ‘ ,
-> master_log_file= ‘ mysql_bin.000001 ‘ ,
-> master_log_pos= 328 ;
Query OK, 0 rows affected ( 0.002 sec)
MariaDB [(none)] >
[root@chouyu ~ ]# systemctl restart mariadb
MariaDB [(none)] > show slave status\G
*************************** 1 . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168 . 220.10
Master_User: xixi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin. 000002
Read_Master_Log_Pos: 328
Relay_Log_File: mysql_relay_bin. 000006
Relay_Log_Pos: 627
Relay_Master_Log_File: mysql_bin. 000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
重启。再查看服务器状态
// 往主库写入 MariaDB [(none)]> create database xixi; Query OK, 1 row affected ( 0.001 sec) MariaDB [(none)] > use xixi; Database changed MariaDB [xixi] > create table test( id int not null auto_increment,name varchar( 20 ),age tinyint,primary key( id )); Query OK, 0 rows affected ( 0.003 sec) MariaDB [xixi] > insert test(name,age) values( ‘ z ‘ , 34 ),( ‘ x ‘ , 24 ),( ‘ c ‘ , 25 ); Query OK, 3 rows affected ( 0.001 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [xixi] > select * from test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ 3 rows in set ( 0.000 sec) MariaDB [xixi] > // 去从库看一下数据同步没有 MariaDB [(none)]> select * from xixi.test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ 3 rows in set ( 0.000 sec) MariaDB [(none)] >搭建两台 MySQL 服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作 主数据库：IP192.168.220.10 有数据 从数据库：IP192.168.220.20 无数据
// 查看主库数据 [root@chouyu ~]# mysql -uroot -p -e ‘ show databases; ‘ Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | xixi | +--------------------+ [root@chouyu ~]# mysql -uroot -p -e ‘ select * from xixi.test; ‘ Enter password: +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ [root@chouyu ~]#
// 从库上没数据 [root@chouyu ~]# mysql -uroot -p -e ‘ show databases; ‘ Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ [root@chouyu ~]#
// 全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致 MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected ( 0.000 sec) MariaDB [(none)] > [root@chouyu ~]# mysqldump -uroot -p --all-databases > all_$( date +%F_% T).sql Enter password: [root@chouyu ~ ]# [root@chouyu ~]# ls all_2021 - 01 -03_08: 54 : 50 .sql all_2021- 01 -03_08: 55 : 27 .sql anaconda- ks.cfg [root@chouyu ~]#
// 将备份数据推送至从服务器 [root@chouyu ~]# scp /root/all_2021- 01 -03_08\: 55 \: 27 .sql ‘ root@192.168.220.20:/root/ ‘ root@ 192.168 . 220.20 ‘ s password: all_2021- 01 -03_08\: 55 \: 27 .sql 100 % 500KB 10 .2MB/s 00 : 00
[root@chouyu ~]# mysql -uroot -p <all_2021- 01 -03_08\: 55 \: 27 .sql Enter password: [root@chouyu ~]# mysql -uroot -p -e ‘ show databases; ‘ [root@chouyu ~]# mysql -uroot -p123123123 -e ‘ show databases; ‘ Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | xixi | +--------------------+ [root@chouyu ~ ]# [root@chouyu ~]# mysql -uroot -p123123123 -e ‘ select * from xixi.test; ‘ Enter password: +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ [root@chouyu ~]#
mysql主从
标签：sele 配置文件 warnings mysqld 导致 iad schema form 数据推送
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did117977