mysql主从同步与读写分离

简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

环境说明

Linux操作系统版本：CentOS Linux release 7.6.1810

数据库版本：MySQL 5.7.37

三台虚机, IP分别为：10.4.7.100, 10.4.7.101, 10.4.7.102

主从复制

运行MySQL服务

docker run -it -d --name mysql \
-e TZ="Asia/Shanghai" \
-p 3306:3306 \
-v /home/docker/mysql/data:/var/lib/mysql \
-v /home/docker/mysql:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.37

配置文件

一般Linux中的MySQL配置文件都在/etc/my.cnf(windows中的配置文件为mysql.ini)
log-bin=mysql-bin 开启二进制日志
注意：二进制日志必须开启，因为数据的同步实质上就是其他的MySQL数据库服务器将这个数据变更的二进制日志在本机上再执行一遍。
10.4.7.100  为主数据库服务器
10.4.7.101和10.4.7.102  为从数据库服务器

修改主配置文件mysqld.cnf

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

character-set-server = utf8   
#skip-networking  
innodb_print_all_deadlocks = 1
max_connections = 2000  
max_connect_errors = 6000  
open_files_limit = 65535  
table_open_cache = 128   
max_allowed_packet = 4M  
binlog_cache_size = 1M  
max_heap_table_size = 8M  
tmp_table_size = 16M  
  
read_buffer_size = 2M  
read_rnd_buffer_size = 8M  
sort_buffer_size = 8M  
join_buffer_size = 28M  
key_buffer_size = 4M  
  
thread_cache_size = 8  
  
query_cache_type = 1  
query_cache_size = 8M  
query_cache_limit = 2M  
  
ft_min_word_len = 4  
  
log-bin = mysql-bin
server-id = 1
binlog_format = mixed  
 
performance_schema = 0  
explicit_defaults_for_timestamp  
  
#lower_case_table_names = 1  
  
interactive_timeout = 28800  
wait_timeout = 28800  

#Recommended in standard MySQL setup  
  
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES   
  
[mysqldump]  
quick  
max_allowed_packet = 16M  
  
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

进入主服务器终端

root@fe842ac59068:/# mysql -uroot -p123456

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 配置从数据库用户及权限
mysql> GRANT REPLICATION SLAVE ON *.* to 'ace'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

然后重启mysql容器：docker restart mysql

修改从配置文件mysqld.cnf

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

character-set-server = utf8   
#skip-networking  
innodb_print_all_deadlocks = 1
max_connections = 2000  
max_connect_errors = 6000  
open_files_limit = 65535  
table_open_cache = 128   
max_allowed_packet = 4M  
binlog_cache_size = 1M  
max_heap_table_size = 8M  
tmp_table_size = 16M  
  
read_buffer_size = 2M  
read_rnd_buffer_size = 8M  
sort_buffer_size = 8M  
join_buffer_size = 28M  
key_buffer_size = 4M  
  
thread_cache_size = 8  
  
query_cache_type = 1  
query_cache_size = 8M  
query_cache_limit = 2M  
  
ft_min_word_len = 4  
  
log-bin = mysql-bin
server-id = 2
binlog_format = mixed  
 
performance_schema = 0  
explicit_defaults_for_timestamp  
  
#lower_case_table_names = 1  
  
interactive_timeout = 28800  
wait_timeout = 28800  

#Recommended in standard MySQL setup  
  
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES   
  
[mysqldump]  
quick  
max_allowed_packet = 16M  
  
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

然后重启mysql容器：docker restart mysql

配置从服务器

[root@node2 mysql]# docker exec -it mysql bash

# 配置主服务器信息
root@b08861e24253:/# mysql -u root -p123456
mysql> change master to master_host='10.4.7.100',master_user='ace',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

从服务器启动I/O 线程和SQL线程

不带任何参数，表示同时启动I/O 线程和SQL线程。 I/O线程从主库读取bin log，并存储到relay log中继日志文件中。 SQL线程读取中继日志，解析后，在从库重放。

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
# Slave_IO_Running: Yes，Slave_SQL_Running: Yes即表示启动成功。
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.4.7.100
                  Master_User: ace
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 588
               Relay_Log_File: b08861e24253-relay-bin.000002
                Relay_Log_Pos: 754
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试

# 主库创建test003库
mysql> create database if not exists test003 default character set = 'utf8';
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test003            |
+--------------------+
5 rows in set (0.00 sec)


# 从库同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test003            |
+--------------------+
5 rows in set (0.00 sec)

注意事项

当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示，可根据错误提示进行更正。
Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。
常见出错点：
     1、两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。
     2、已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。
     3、stop slave后，数据变更，再start slave。出错。
     终极更正法：重新执行一遍CHANGE MASTER就好了。

读写分离