mariadb-10.4主从配置
主从机器分别如下
master 192.168.80.137
slave 192.168.80.138
1、master安装的mysql开启bin-log模式
[root@localhost ~]# vi /etc/my.cnf
# 开启binlog
log-bin=mysql-bin
# 添加不同步数据的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
# 配置server_id
server-id=1
[root@localhost ~]# systemctl restart mariadb
2、slave开启中继模式
[root@localhost ~]# vi /etc/my.cnf
# 开启中继日志(/var/lib/mysql/为mysql配置文件中的datadir)
relay-log=/var/lib/mysql/relay-bin
# 限制从服务器只读
read_only=1
# 修改server_id
server-id=2
[root@localhost ~]# systemctl restart mariadb
3、master上创建复制账号
- 创建
admin_replicate账号
MariaDB [(none)]> grant replication slave,replication client on *.* to 'admin_replicate'@'192.168.80.138' identified by 'a123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 查看主库二进制日志状态，并记录开始复制的位置(position)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000002 | 504 | | information_schema,mysql,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
4、在从库上使用复制账号登录主库，并指定复制位置
[root@localhost mariadb]# systemctl restart mariadb
[root@localhost mariadb]#
[root@localhost mariadb]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
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)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> change master to master_host='192.168.80.137',master_port=3306,master_user='admin_replicate',master_password='a123456',master_log_file='mysql-bin.000002',master_log_pos=504;
Query OK, 0 rows affected (0.01 sec)
- 执行过程中如遇出错可重置slave节点
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
5、查看slave状态
MariaDB [(none)]> show slave status;
+----------------+----------------+---------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------+----------------+---------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| | 192.168.80.137 | admin_replicate | 3306 | 60 | mysql-bin.000002 | 504 | relay-bin.000001 | 4 | mysql-bin.000002 | No | No | | | | | | | 0 | | 0 | 504 | 245 | None | | 0 | No | | | | | | NULL | No | 0 | | 0 | | | 0 |
+----------------+----------------+---------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
可以看到Slave_IO_Running | Slave_SQL_Running 均为No，说明未开始同步
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
再观察运行状态可以发现已经正常
MariaDB [(none)]> show slave status;
+----------------------------------+----------------+---------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+----------------+---------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | 192.168.80.137 | admin_replicate | 3306 | 60 | mysql-bin.000002 | 504 | relay-bin.000002 | 529 | mysql-bin.000002 | Yes | Yes | | | | | | | 0 | | 0 | 504 | 817 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 |
+----------------------------------+----------------+---------------+-------------+---------------+------------------+---------------------+------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
6、查看master状态
MariaDB [(none)]> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 2558 |
| mysql-bin.000003 | 12398 |
| mysql-bin.000004 | 2400927 |
| mysql-bin.000005 | 264 |
| mysql-bin.000006 | 1251 |
+------------------+-----------+
6 rows in set (0.01 sec)
MariaDB [(none)]> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 1251
Binlog_Do_DB:
Binlog_Ignore_DB: information_schema,mysql,performance_schema
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(none)]> show binlog events;
+------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 245 | Server ver: 5.5.65-MariaDB, Binlog ver: 4 |
| mysql-bin.000001 | 245 | Stop | 1 | 264 | |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------+
2 rows in set (0.02 sec)
MariaDB [(none)]>
7、从库重启
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
8、主从同步故障处理（数据不一致）
安装检测工具
yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
yum install wget -y
wget https://www.percona.com/redir/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm --no-check-certificate
yum install percona-release-0.1-6.noarch.rpm -y
yum install percona-toolkit -y --nogpgcheck
检查是否有不一样的数据
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=test.checksums --databases=test h=192.168.80.137,u=root,p=root132,p=3306
先print检查异常数据
pt-table-sync --replicate=test.checksums h=192.168.80.137,u=root,p=root123 h=192.168.80.138,u=root,p=root123 --print
处理异常数据
pt-table-sync --replicate=test.checksums h=192.168.80.137,u=root,p=root123 h=192.168.80.138,u=root,p=root123 --execute
赞赏(Donation)
微信(Wechat Pay)
