MySQL配置主从同步

需要两台服务器,mysql大版本需要相同,即主库是MySQL5.7,那么从库也要是MySQL5.7,禁止双端版本不一致

注意: 在开始主从同步时,需要保证两台服务器上数据库的数据已经一致,然后才可进行主从同步操作,主从同步操作完成后,可以测试在主库insert或update一条数据,看下从库是否同步变更

开始搭建配置:以系统ubuntu16.04-MySQL5.7为例

主库和从库的mysql都要把原有的bind-address = 127.0.0.1注释掉,并让外网可访问

一. 配置主库MySQL

1. 安装好mysql后打开mysql的配置文件vi /etc/mysql/mysql.conf.d/mysqld.cnf(其它系统自行查询配置文件位置),添加以下配置信息:

   server-id = 190  # 必填,mysql服务端口,一般采用ip最后一段
   log_bin = /var/log/mysql/mysql-bin.log  # 必填,binlog文件位置,用于同步用
   binlog_do_db = test  # 选填,指定同步哪一个库,多个库就添加多行,一行一个库
   #binlog_ignore_db = include_database_name  # 选填,忽略同步哪一个库,多个库就添加多行,一般来说有指定同步哪个库后,就可以不用写这一行
   

2. 进入Mysql控制台,执行下列命令,用于配置同步账号:

   mysql> grant replication slave on *.* to slave@192.168.1.11 identified by '123456'; # 配置给从库使用的账号/密码,其中192.168.1.11指的是从库所在服务器ip
   mysql> flush privileges; # 刷新权限,这一步配置完成后,需要退出mysql控制台,出去重启mysql服务,不然不生效
   mysql> show master status; # 查看主库状态,记录下File和Position
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      337 |              |                  |
   +------------------+----------+--------------+------------------+
   

二. 配置从库MySQL

1. 安装好mysql后打开mysql的配置文件vi /etc/mysql/mysql.conf.d/mysqld.cnf(其它系统自行查询配置文件位置),添加以下配置信息:(由于是从库,只需要添加以下两个信息即可)

   server-id = 131  # 必填,mysql服务端口,一般采用ip最后一段
   log_bin = /var/log/mysql/mysql-bin.log  # 必填,用于记录Mysql操作信息
   

2. 进入mysql控制台,配置连接主库:

   mysql> change master to master_host='192.168.1.10',master_user='slave',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=337;
   # 参数详解:
   # master_host: 主库所在服务器的ip
   # master_user: 主库给从库允许的账号
   # master_password: 主库给从库对应账号的密码
   # master_log_file: 主库中查询到的File名字
   # master_log_pos: 主库中查询到的Position端口信息
   

3. 开始同步主库数据:

   mysql> start slave; # 开启从库同步
   mysql> show slave status\G; # 显示从库状态,如看到以下信息中Slave_IO_Running和Slave_SQL_Running都是yes即表示成功
   
   *************************** 1. row ***************************
                  Slave_IO_State: Waiting for master to send event
                     Master_Host: 192.168.1.10
                     Master_User: slave
                     Master_Port: 3306
                   Connect_Retry: 60
                 Master_Log_File: mysql-bin.000001
             Read_Master_Log_Pos: 154
                  Relay_Log_File: vultr-relay-bin.000002
                   Relay_Log_Pos: 320
           Relay_Master_Log_File: mysql-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: 527
                 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: 190
                     Master_UUID: 149153aa-0108-11ea-84d5-560002678d82
                Master_Info_File: /var/lib/mysql/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)
   
   

相关命令:

mysql> start slave; # 开启从库同步
mysql> stop slave; # 停止从库同步
mysql> show slave satatus; # 显示从库状态
mysql> show master status; # 显示主库状态
mysql> show processlist; # 显示mysql子进程列表
mysql> set global sql_slave_skip_counter =1; # 设置跳过错误步数，数字任意
mysql> flush tables with read lock; # 锁表，防止数据写入（同步失败时，或备份时使用）
mysql> unlock tables # 解锁
mysql> source /tmp/mysql.bak.sql # 导入mysql.bak.sql文件（导入前设置操作的数据库）
mysql> change master to master_host = '192.168.128.10', master_user = 'slave', master_port=3306, 
mysql> master_password='123456', master_log_file = 'mysqld-bin.000001', master_log_pos=3260; # 修改链接到主库的信息；
mysql> change master to master_user='repl', master_password='replpwd'; # 修改链接到主库的帐号信息
mysql> show grants for 'repl'@'192.168.1.177' # 显示复制帐号信息
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.177' IDENTIFIED BY 'replpwd'; # 修改帐号密码
mysql> system grep repl /data/inst3506/data3506/master.info // 使用系统命令查看mysql生成的主库信息