MySQL 主从复制

Mysql 的安装不再介绍, 直接说明 Master 和 Slave 的配置

Master 配置:

[mysqld]

datadir=/data/mysql

socket=/tmp/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-bin=master-bin #启用二进制日志

binlog_format=mixed #二进制日志格式使用混合模式

server-id=1 #服务器id,范围为{0-2^32}

innodb_file_per_table=1 #每一个innodb表使用独立的文件

sync-binlog=1 #为保证事物安全, 在master上启用同步二进制日志功能

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

启动mysqld, 创建复制用户:

[root@keep1 ~]# service mysqld start

Starting MySQL...... SUCCESS!

[root@keep1 ~]# mysql

...

mysql> grant replication slave on *.* to 'repluser'@'10.11.8.%' identified by 'replpass';

Query OK, 0 rows affected (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Slave 配置:

[mysqld]

datadir=/data/mysql

socket=/tmp/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

#log-bin=master-bin

binlog_format=mixed

server-id=10

innodb_file_per_table=1

relay-log=relay-log

read-only=1 #为保证数据, 在slave上启用只读, 对具有super权限的用户不生效

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

启动mysqld 服务:

[root@keep2 ~]# service mysqld start

Starting MySQL...... SUCCESS!

Master 节点查看二进制日志状态:

mysql> show master status;

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

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| master-bin.000002 |      409 |              |                  |                   |

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

1 row in set (0.00 sec)

Slave 上 配置连接, 查看状态, 启动slave :

mysql> change master to master_host='10.11.8.219',master_user='repluser',master_password='replpass',master_log_file='master-bin.000002',master_log_pos=409;

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

mysql> show slave status\G

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

              Slave_IO_State:

                 Master_Host: 10.11.8.219

                 Master_User: repluser

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: master-bin.000002

         Read_Master_Log_Pos: 409

              Relay_Log_File: relay-log.000001

               Relay_Log_Pos: 4

       Relay_Master_Log_File: master-bin.000002

            Slave_IO_Running: No #此时IO和SQL都处于停止状态

           Slave_SQL_Running: No

             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: 409 #slavev上执行到了第几个

             Relay_Log_Space: 120

             Until_Condition: None

              Until_Log_File:

               Until_Log_Pos: 0

          Master_SSL_Allowed: No #是否允许使用SSL

          Master_SSL_CA_File:

          Master_SSL_CA_Path:

             Master_SSL_Cert:

           Master_SSL_Cipher:

              Master_SSL_Key:

       Seconds_Behind_Master: NULL #slave比master慢多少

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

                 Master_UUID:

            Master_Info_File: /data/mysql/master.info

                   SQL_Delay: 0

         SQL_Remaining_Delay: NULL

     Slave_SQL_Running_State:

          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

1 row in set (0.00 sec)

mysql> start slave; #启动Slave

Query OK, 0 rows affected (0.00 sec)



mysql> show slave status\G

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

              Slave_IO_State: Waiting for master to send event

                 Master_Host: 10.11.8.219

                 Master_User: repluser

                 Master_Port: 3306

               Connect_Retry: 60

             Master_Log_File: master-bin.000002

         Read_Master_Log_Pos: 409

              Relay_Log_File: relay-log.000002

               Relay_Log_Pos: 284

       Relay_Master_Log_File: master-bin.000002

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

             Relay_Log_Space: 451

             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: 441052e5-28c9-11e6-9cd1-0800274f81c9

            Master_Info_File: /data/mysql/master.info

                   SQL_Delay: 0

         SQL_Remaining_Delay: NULL

     Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

          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

1 row in set (0.00 sec)

Master 上添加数据, 测试同步:

Master 创建一个库:



mysql> create database ktt;

Query OK, 1 row affected (0.01 sec)

Slave 查看状态:

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.11.8.219

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000002

          Read_Master_Log_Pos: 500

               Relay_Log_File: relay-log.000002

                Relay_Log_Pos: 375

        Relay_Master_Log_File: master-bin.000002

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

              Relay_Log_Space: 542

              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: 441052e5-28c9-11e6-9cd1-0800274f81c9

             Master_Info_File: /data/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           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

1 row in set (0.00 sec)

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| ktt                |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.01 sec)

补充: Slave 重启后可自动启动slave 进程是因为保存了相关的连接信息, 即以下两个文件

[root@keep2 mysql]# cat master.info

23

master-bin.000002

500

10.11.8.219

repluser

replpass

3306

60

0

 

 

 

 

 

0

1800.000

 

0

441052e5-28c9-11e6-9cd1-0800274f81c9

86400

 

 

0

[root@keep2 mysql]# cat relay-log.info

7

./relay-log.000004

284

master-bin.000002

500

0

0

1