Linux Centos7下配置Mysql5.7主从同步

数据库安装参考：https://blog.youkuaiyun.com/STIll_ly/article/details/103561594

环境准备

Linux发行版本：Centos7
Mysql数据库版本：mysql-5.7.24
主服务器地址：10.10.10.8
从服务器地址：10.10.10.9

配置主服务器

1. 首先root用户登录mysql，创建一个同步账号，授予该账号REPLICATION SLAVE权限，用于从服务器访问主服务器

[admin@localhost mysql]$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'repl'@'IP段' IDENTIFIED BY '密码';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'IP段';
Query OK, 0 rows affected (0.00 sec)

2. 配置主从同步，要在主服务器开启二进制日志，也就是binlog，并配置要同步或不同步哪些数据库.这需要修改my.cnf配置文件

[admin@localhost etc]$ cat my.cnf
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
port = 3306
default-character-set=utf8

[mysqld]
# 一般配置选项
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character-set-server=utf8
default_storage_engine = InnoDB

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#此参数表示启用binlog并指定日志文件路径
log_bin=master-bin
#此参数指定二进制索引文件路径
log_bin_index=master-bin.index
#配置服务器的唯一标识,和其他服务器的唯一标识必须不同,从服务器通过该标识找到主服务器
server-id=1
#指定范围日期内的binlog日志,过期的日志会被清除
expire-logs-days=7
#配置不记录binlog日志的数据库,没有binlog日志的数据库则不会被同步
binlog_ignore_db=mysql
#这些数据库都是mysql系统数据库
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
binlog_ignore_db=sys
#配置记录binlog日志的数据库,有binlog日志的数据库会被同步，和binlog_ignore_db参数互斥，只需配置一类即可
#binlog_do_db=

3. 配置完成后,重启mysql服务：

[admin@localhost etc]$ sudo service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

4. 登录mysql,查看master状态

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

记录下File和Position这两个值,一会会用到。

配置从服务器

1. 修改从数据库的my.cnf

[root@localhost etc]# cat my.cnf
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
port = 3306
default-character-set=utf8

[mysqld]
# 一般配置选项
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character-set-server=utf8
default_storage_engine = InnoDB

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#服务器唯一标识
server-id = 2
#启动中继日志服务并设置地址,中继日志就是存储主库过来的binlog日志
relay-log = slave-relay-bin
#中继日志二进制文件索引地址
relay-log-index = slave-relay-bin.index

2. 重启mysql服务,然后登陆从服务器的mysql,配置主服务器相关设置，并启动SLAVE

[root@localhost etc]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost etc]# mysql -uroot -p
Enter password: 
mysql> change master to master_host='主IP', master_port=3306, master_user='repl', master_password='repl密码', master_log_file='master-bin.000001', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

3. 查看主从状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 主IP
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-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: 154
              Relay_Log_Space: 528
              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_UUID: fb1a625d-1fd2-11ea-9f77-0cda411d2303
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Slave_IO_Running和Slave_SQL_Running都为Yes,表示成功，完美！

4. 测试
   主服务器创建一个数据库testdb：

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

登录从服务器查看数据库状态：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.01 sec)

在主服务器中创建的数据库testdb，在从服务器中也能查看到，说明主从同步成功，完美！

数据初始化

对于有初始数据的主从同步，可以使用mysqldump命令先导出主库数据，然后将数据导入从库后再做主从
1、主数据导出：
nohup mysqldump -h主库IP -u主库用户名 -p主库密码 --single-transaction --master-data -B dbname1 dbname2 dbname3… > all.sql&
2、登录从库执行命令 source ./all.sql
3、启动从库，change master to master_host=‘主IP’, master_port=3306, master_user=‘repl’, master_password=‘repl密码’, master_log_file=‘master-bin.000001’, master_log_pos=154;
命令中的master_log_file和master_log_pos参数可以使用more命令在all.sql中查看