Mariadb实现主从复制（一主一从（M-S））

一、原理： 概念 1. 在主库上把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。 2. 备库I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。 3. 备库SQL线程读取中继日志中的事件，将其重放到备份数据库之上。 二、图示： 三、准备工作 准备两台主机 ，关闭两台主机的防火墙或者为mysql服务设置规则，并做好host域名解析。 192.168.231.31 master 192.168.231.32 slave 对master操作：

# yum install mariadb mariadb-server -y # systemctl start mariadb # systemctl enable mariadb # mysqladmin -uroot password 'Wiger@5111' # mysql -p'Wiger@5111' MariaDB [(none)]> create database jike1701; MariaDB [(none)]> create table jike1701.t1(id int,name char(20)); MariaDB [(none)]> insert into jike1701 values(1,'chen'); MariaDB [(none)]> insert into jike1701 values(2,'zhang'); MariaDB [(none)]> select * from jike1701.t1; -> ; +------+-------+ | id | name | +------+-------+ | 1 | chen | | 2 | zhang | +------+-------+

对slave操作

# yum install mariadb mariadb-server -y # systemctl start mariadb # systemctl enable mariadb # mysqladmin -uroot password 'Wiger@5111'

四、“干货开始” 对master操作： 1.添加箭头所指的两行

# vim /etc/my.cnf

2.重启

# systemctl restart mariadb

3.登录+授权+日志文件

# mysql -p'Wiger@5111' MariaDB [(none)]> grant replication slave,replication client on *.* to 'wiger'@''192.168.231.%' identified by 'Wiger@5222'; MariaDB [(none)]>exit # mysqldump -p'Wiger@5111' --all-databases --single-transaction --master-data=1 --flush-logs > `date +%F`-mysql-all.sql

4.发送日志文件到slave主机

# scp 2020-07-01-mysql-all.sql slave:/root

5.查看日志文件,出现箭头所指样式即可

# cat 2020-07-01-mysql-all.sql slave:/root

对slave操作： 1.测试master授权的用户能否链接上主服务器；要确保可以连接如下代码所示：

[root@slave ~]# mysql -hmaster -uwiger -p'Wiger@5222' Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 5.5.65-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. MariaDB [(none)]> show grants; +--------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for wiger@192.168.231.% | +--------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'wiger'@'192.168.231.%' IDENTIFIED BY PASSWORD '*6A3DD188ABAC680C94D1004C93B1171B2965BDD7' | +--------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> exit Bye

2.修改配置文件,添加如箭头所在行

# vim /etc/my.cnf

3.重启MySQL服务

# systemctl restart mariadb

4.复制操作

# mysql -p'Wiger@5111' MariaDB [(none)]> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> source /root/2020-07-01-mysql-all.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ... MariaDB [test]> change master to master_host='master', master_user='wiger',master_password='Wiger@5222'; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: master Master_User: wiger Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: mariadb-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Connecting Slave_SQL_Running: Yes #要出现Yes Replicate_Do_DB: Yes #要出现Yes Replicate_Ignore_DB: Replicate_Do_Table: ...

5.查看数据库

MariaDB [test]> select * from jike1701.t1 ; +------+-------+ | id | name | +------+-------+ | 1 | chen | | 2 | zhang | +------+-------+

完~

值得注意： master数据库上的数据（增、删、改）可以同步到slave上； slave数据库上的数据（增、删、改）不可以同步到master服务器上； 从而衍生出读写分离的机制； 待下回分解。。。