MySQL主从复制实战

MySQL主从复制实战

Master

系统环境准备

Master：192.168.140.128
Slave：192.168.140.129
yum –y install mariadb mariadb-server
systemctl start mariadb
systemctl enable mariadb
mysql_secure_installation

配置文件/etc/my.cnf

[root@master ~]# grep -v '#' /etc/my.cnf

	[client]
	port = 3306
	socket=/var/lib/mysql/mysql.sock
	
	[mysqld]
	port = 3306
	datadir=/var/lib/mysql
	socket=/var/lib/mysql/mysql.sock
	skip-external-locking
	key_buffer_size = 256M
	max_allowed_packet = 1MB
	table_open_cache = 256
	sort_buffer_size = 1MB
	read_buffer_size = 1MB
	read_rnd_buffer_size = 4MB
	myisam_sort_buffer_size = 64MB
	thread_cache_size = 8
	query_cache_size = 16MB
	thread_concurrency = 8
	
	log-bin = mysql-bin
	binlog_format = mixed
	server-id = 1
	
	[mysqldump]
	quick
	max_allowed_packer = 16MB
	
	[mysql]
	no-auto-rehash
	
	[myisamchk]
	key_buffer_size = 128MB
	sort_buffer_size = 128MB
	read_buffer = 2MB
	write_buffer = 2MB
	
	[mysqlhotcopy]
	interactive-timeout

数据库配置

数据库服务器命令创建用户及密码并设置权限。

MariaDB[(none)]>grant replication slave on *.* to 'test'@'%'identified by 'test';
Query OK, 0 rows affected (0.00 sec)

查看bin-log文件及position点。

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |     1610 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

创建数据库，创建表，插入数据

MariaDB[(none)]> create database mysql_ab_test charset=utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_ab_test      |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

MariaDB[(none)]> use mysql_ab_test;
Database changed
MariaDB[mysql_ab_test]> create table t0 (id varchar(20),name varchar(20));
Query OK, 0 rows affected (0.00 sec)

MariaDB[mysql_ab_test]> show tables;
+-------------------------+
| Tables_in_mysql_ab_test |
+-------------------------+
| t0                      |
+-------------------------+
1 row in set (0.00 sec)

MariaDB[mysql_ab_test]> insert into t0 values ('001','zhangsan');
Query OK, 1 row affected (0.01 sec)

MariaDB[mysql_ab_test]> insert into t0 values ('002','lisi');
Query OK, 1 row affected (0.00 sec)

Slave

配置文件/etc/my.cnf

[root@slave ~]# grep -v '#' /etc/my.cnf
	[client]
	port = 3306
	socket=/var/lib/mysql/mysql.sock
	
	[mysqld]
	port = 3306
	skip-external-locking
	key_buffer_size = 256M
	max_allowed_packet = 1MB
	table_open_cache = 256
	sort_buffer_size = 1MB
	read_buffer_size = 1MB
	read_rnd_buffer_size = 4MB
	myisam_sort_buffer_size = 64MB
	thread_cache_size = 8
	query_cache_size = 16MB
	thread_concurrency = 8
	
	datadir=/var/lib/mysql
	socket=/var/lib/mysql/mysql.sock
	
	server-id = 2
	
	[mysqldump]
	quick
	max_allowed_packer = 16MB
	
	[mysql]
	no-auto-rehash
	
	[myisamchk]
	key_buffer_size = 128MB
	sort_buffer_size = 128MB
	read_buffer = 2MB
	write_buffer = 2MB
	
	[mysqlhotcopy]
	interactive-timeout

数据库配置

Slave指定master IP、用户名、密码、Bin-log文件名及position。

MariaDB[(none)]>change	master	to master_host='192.168.140.128',master_user='test',master_password='test',master_log_file='mysql-bin.000005',master_log_pos=1610;

Query OK, 0 rows affected (0.04 sec)

在slave中启动slave start，并执行show slave status\G 查看mysql主从状态。

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.140.128
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1610
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000005
             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: 1610
              Relay_Log_Space: 825
              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
1 row in set (0.00 sec)

查看是否复制成功

查看是否存在数据库，表，数据

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_ab_test      |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use mysql_ab_test;
Database changed

MariaDB [mysql_ab_test]> show tables;
+-------------------------+
| Tables_in_mysql_ab_test |
+-------------------------+
| t0                      |
+-------------------------+
1 row in set (0.00 sec)

MariaDB [mysql_ab_test]> select * from t0;
+------+----------+
| id   | name     |
+------+----------+
| 001  | zhangsan |
| 002  | lisi     |
+------+----------+
2 rows in set (0.00 sec)

Mysql 主从同步排错思路

忽略错误后，继续同步

此方法适用于主从库数据内容相差不大的情况。
Master端：
将数据库设置为全局读锁，不允许写入新数据。

Flush tables with read lock;

Slave端：
停止slave I/O及sql线程，同时将同步错误的sql跳过一次，跳过错误会导致数据不一致，启动start slave，同步状态恢复。

Stop slave；
Set global sql_slave_skip_counter=1;
Start slave;

重新做主从同步，使数据完全同步。

此种方法适用于主从库数据内容相差很大的情况。
Master端：
将数据库设置全局读锁，不允许写入新数据。

Flush tables with read lock；

Master端基于mysqldump、xtrabackup工具对数据库进行完整备份，也可以用shell脚本或Python脚本实现定时备份，备份成功之后，将完整的数据导入至从库，重新配置主从关系，当slave端的I/O线程、sql线程均为yes之后，最后将master端读锁解开即可，解锁命令如下：

unlock tables；