mysql5.7 主从同步

一、安装配置mysql

参照前篇：centos7 mysql安装

二、配置主从服务器

1、配置主从服务器配置文件

主服务器配置

vi /etc/mycnf

添加

log_bin=mysql_bin         开启二进制人间

server_id=1               设置服务器ID

重启master

systemctl  restart mysqld

为slave配置账号

mysql> create user backup@'192.168.121.22' identified by '775120@Lai';

mysql> grant replication slave on *.* to backup@'192.168.121.22';

mysql> flush privileges;   (这一步是必须的）

 查看配置状况

mysql> show grants for backup@192.168.121.22;

+-------------------------------------------------------------+

| Grants for backup@192.168.121.22                            |

+-------------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.121.22' |

+-------------------------------------------------------------+

1 row in set (0.00 sec)

然后查看master参数

show msater status;

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000006 |      154 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

至此暂停master上的操作，防止修改参数：mysql-bin.000006

从服务器上配置

log_bin=mysql-bin

server-id=2

relay_log=mysql-relay-bin           中继日志

log_slave_updates=1                 将复制事件写进自己的二进制日志

read_only=1                         只读

说明：1、slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log

          2、开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置

          3、read_only，它防止改变数据(除了特殊的线程)

重启slave

systemctl  restart mysqld

配置slave上的mysql

mysql> change master to master_host='192.168.121.21',

    -> master_user='backup',

    -> master_password='775120@Lai',

    -> master_log_file='mysql-bin.000006',

    -> master_log_pos=0;

Query OK, 0 rows affected, 2 warnings (0.01 sec)

MASTER_LOG_POS的值为0，因为它是日志的开始位置。

启动slave

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

查看slave的状态

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Connecting to master

                  Master_Host: 192.168.121.21

                  Master_User: backup

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 4

               Relay_Log_File: mysql-relay-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Connecting

            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: 4

              Relay_Log_Space: 154

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 2003

                Last_IO_Error: error connecting to master 'backup@192.168.121.21:3306' - retry-time: 60  retries: 1

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 0

                  Master_UUID:

             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: 170904 11:22:33

     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)

此处显示连不上master

排除账号，密码问题，应该是网络问题

关闭master上的防火墙

再查看slave状态

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.121.21

                  Master_User: backup

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 154

               Relay_Log_File: mysql-relay-bin.000002

                Relay_Log_Pos: 367

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

             

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

表明配置成功

三、测试

1、在master上创建新的数据库

mysql> create database schllo;

Query OK, 1 row affected (0.00 sec)

slave上查看数据库状况

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| schllo             |

| sys                |

+--------------------+

5 rows in set (0.01 sec)

2、master上删除数据库

mysql> drop database schllo;

Query OK, 0 rows affected (0.00 sec)

slave上查看数据库状况

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

四、连接不通

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Connecting to master

                  Master_Host: 192.168.121.21

                  Master_User: backup

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000006

          Read_Master_Log_Pos: 4

               Relay_Log_File: mysql-relay-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000006

             Slave_IO_Running: Connecting

            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: 4

              Relay_Log_Space: 154

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 2003

                Last_IO_Error: error connecting to master 'backup@192.168.121.21:3306' - retry-time: 60  retries: 1

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 0

                  Master_UUID:

             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: 170904 11:22:33

     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)

ERROR:

No query specified

error connecting to master 'backup@192.168.121.21:3306' - retry-time: 60  retries: 1

疑似网络不通

查看iptables

解决方法：

1、关闭iptables
2、iptables -F

后续联合iptables一起使用，配置通道

[root@centos-0 lcr]# mysql -u root -p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

[root@centos-0 lcr]# systemctl start mysqld

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@centos-0 lcr]#

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@centos-0 lcr]# systemctl status mysqld.service

● mysqld.service - MySQL Server

   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

   Active: deactivating (final-sigterm) (Result: exit-code)

     Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 7585 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)

  Process: 7563 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

   CGroup: /system.slice/mysqld.service

           └─7588 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mys...

9月 04 14:16:30 centos-0 systemd[1]: Starting MySQL Server...

9月 04 14:16:31 centos-0 mysqld[7585]: Initialization of mysqld failed: 0

9月 04 14:16:31 centos-0 systemd[1]: mysqld.service: control process exite...=1

Hint: Some lines were ellipsized, use -l to show in full.

SUCCESS)

   CGroup: /system.slice/mysqld.service

           └─7588 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mys...

9月 04 14:16:30 centos-0 systemd[1]: Starting MySQL Server...

9月 04 14:16:31 centos-0 mysqld[7585]: Initialization of mysqld failed: 0

9月 04 14:16:31 centos-0 systemd[1]: mysqld.service: control process exite...=1

Hint: Some lines were ellipsized, use -l to show in full.

[root@centos-0 lcr]# journalctl -xe

-- Defined-By: systemd

-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

--

-- Unit mysqld.service has begun starting up.

9月 04 14:18:26 centos-0 mysqld[9648]: Initialization of mysqld failed: 0

9月 04 14:18:26 centos-0 systemd[1]: mysqld.service: control process exited, cod

9月 04 14:18:27 centos-0 systemd[1]: Failed to start MySQL Server.

-- Subject: Unit mysqld.service has failed

-- Defined-By: systemd

-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

--

-- Unit mysqld.service has failed.

--

-- The result is failed.

9月 04 14:18:27 centos-0 systemd[1]: Unit mysqld.service entered failed state.

9月 04 14:18:27 centos-0 systemd[1]: mysqld.service failed.

9月 04 14:18:28 centos-0 systemd[1]: mysqld.service holdoff time over, schedulin

9月 04 14:18:28 centos-0 systemd[1]: Starting MySQL Server...

-- Subject: Unit mysqld.service has begun start-up

-- Defined-By: systemd

-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

--

-- Unit mysqld.service has begun starting up.

lines 2562-2584/2584 (END)