mysql同步大师 绿色_大师网-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