Mariadb数据库主从同步

一、主 (master) 数据库配置

1./etc/my.cnf 添加配置

[mariadb]
log-bin
server_id=1
log-basename=master1
binlog-format=mixed
max_binlog_size=200M
expire_logs_days=7

server_id 必须唯一。
log-basename 是指定 binlog 的命名规则， binlog 会以它为前缀生成日志，如 master1-bin.000001。
max_binlog_size=200M 生成的 log 最大值，到达最大值，会重新创建一个，如 master1-bin.000002。
expire_logs_days binlog 过期天数。

2. 然后重启数据库生效。

systemctl restart mariadb

3. 创建执行同步的数据库用户

//用户名： replication_user， 密码： PWD，可以修改成你想要的。
//授予仅同步权限
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'PWD';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
   
//刷新授权
flush privileges;
   
//查看所有用户
MariaDB [(none)]> SELECT User, Host, Password FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | Password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | ***************************************** |
| root             | 127.0.0.1 | ***************************************** |
| root             | ::1       | ***************************************** |
| replication_user | %         | ***************************************** |
+------------------+-----------+-------------------------------------------+

4. 锁定数据库

FLUSH TABLES WITH READ LOCK;

在导出数据库时，先加锁，避免在导出时修改了数据库导致数据不一致。

5. 记录当前的同步位置

MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 |  1379828 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

当前 master 生成的日志文件: master1-bin.000001,
日志位置： 1379828。

6. 导出数据库

//查看所有数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db01               |
| db02               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)

//备份数据库
mysqldump -u root -pPWD --databases db01 db02 db03 | gzip > dump.master.gz

db01 db02 db03 替换为你的数据库名

7. 解锁数据库

UNLOCK TABLES ;

二、从 (slave) 数据库配置

1./etc/my.cnf 添加配置

[mariadb]
#log-bin
server_id=2

注意，从服务器可以不用开启 bin-log，将 bin-log 注释也可以。

2. 将主服务器导出的数据库导入到从服务器

//主服务器scp传送文件
scp dump.master.gz root@从服务器IP:/root/dump.master.gz

//从服务器将gz文件解压缩
gunzip dump.master.gz

//导入 slave mysql
mysql -uroot -pPWD < dump.master

3. 登录数据库，设置主服务器信息

mysql -uroot -pPWD

change master to 
    master_host='主服务器IP',
    master_user='replication_user',
    master_password='PWD',
    master_port=3306,
    master_log_file='master1-bin.000001',
    master_log_pos=1379828,
    master_connect_retry=10;

4. 然后重启数据库生效。

systemctl restart mariadb

5. 启动同步

start slave;

6. 查看是否同步成功

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 主服务器IP
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master1-bin.000001
Read_Master_Log_Pos: 1468860
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 133159
Relay_Master_Log_File: master1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1468860
Relay_Log_Space: 133470
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 9
Slave_Transactional_Groups: 433