MySQL 5.6搭建主从复制

使用MySQL 5.6,搭建主从复制。关于5.6的安装,可以参考《MySQL 5.6 rpm安装方法和碰见的问题》。


主库创建slave用户,设置复制权限,

mysql> create user 'slave'@'1.1.1.2' identified by 'root';

Query OK, 0 rows affected (0.00 sec)


mysql> grant replication slave on *.* to 'slave'@'1.1.1.2' identified by 'root';

Query OK, 0 rows affected (0.00 sec)


编辑my.cnf配置文件,设置主库server-id=1,定义需要复制的库为test,忽略mysql数据库

[root@vm-kvm10000-app mysql]# vi /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-bin

binlog_do_db=test

binlog_ignore_db=mysql


重启主库MySQL服务,

[root@vm-kvm10000-app mysql]# service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!


看一下主库状态,

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000002 |      120 | test         | mysql            |                   |

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

1 row in set (0.00 sec)


或者

mysql> show master status \G

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

File: mysql-bin.000002

Position: 120

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

Executed_Gtid_Set:

1 row in set (0.00 sec)


从库,编辑my.cnf配置,设置server-id=2,区别于主库,

[root@vm-kvm10001-app mysql]# vi /etc/my.cnf

[mysqld]

server-id=2


重启MySQL服务,

[root@vm-kvm10001-app mysql]# service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!


设置主库信息,

mysql> change master to master_host='1.1.1.1',master_user='slave',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=120,master_connect_retry=10;

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


检索从库状态,

mysql> show slave status \G;

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

Slave_IO_State:

Master_Host: 10.221.3.129

Master_User: slave

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File: vm-kvm11853-app-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

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

Relay_Log_Space: 120

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

Last_IO_Error:

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:

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)


其中最重要的就是,这两个参数,要求YES,但此处为NO,

Slave_IO_Running: No

Slave_SQL_Running: No


检索错误日志,提示无法找见./performance_schema/cond_instances.frm文件,

[root@vm-kvm10001-app mysql]# vm-kvm10001-app.err

2017-08-29 16:10:37 22933 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/cond_instances.frm' (errno: 13 - Permission denied)


看一下其目录,发现performance_schema文件夹,是root权限,mysql用户无法访问,因此需要修改一下其权限,

[root@vm-kvm10001-app mysql]# ls -rlht

total 109M

...

drwx------ 2 root  root  4.0K Aug 29 15:05 performance_schema

...


[root@vm-kvm10001-app mysql]# chown -R mysql:mysql *


[root@vm-kvm10001-app mysql]# ls -rlht

total 109M

...

drwx------ 2 mysql mysql 4.0K Aug 29 15:05 performance_schema

...


重启服务,

[root@vm-kvm10001-app mysql]# service mysql restart

Shutting down MySQL.... SUCCESS!

Starting MySQL. SUCCESS!


再看从库状态,

mysql> show slave status \G

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

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...


主库导出数据,用于导入从库,首先需要设置读锁,避免数据不一致,

mysql> flush tables with read lock;


mysql> show master logs;

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

| Log_name         | File_size |

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

| mysql-bin.000001 |       143 |

| mysql-bin.000002 |       222 |

| mysql-bin.000003 |       545 |

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

3 rows in set (0.00 sec)


执行mysqldump将test库,导出test.sql文件,

[root@vm-kvm10000-app mysql]# mysqldump -uroot -p -B test > test.sql


然后解锁表,

mysql> unlock tables;


从库执行导入,

[root@vm-kvm10001-app mysql]# mysql -uroot -p < test.sql


要确保从库,这两个值正确,

Slave_IO_Running: Yes    
Slave_SQL_Running: Yes


此时就完成了主从复制,向主库插入一条记录,

mysql> INSERT INTO test
    -> (id, name)
    -> VALUES
    -> (1, "a");
Query OK, 1 rows affected (0.00 sec)


从库中可以检索出来,

mysql> select * from test;

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

|     id    |  name  |

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

|      1    |     a      |

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



总结:
1. MySQL相关文件、文件夹需要的权限,可能会因为不正确,例如需要mysql权限,但却是root,导致数据库异常。

2. 主从复制,需要关注从库,这两个参数值,需要均为YES,出现NO,则可以检索错误日志,进一步定位。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes



如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

MySQL 5.6搭建主从复制

上一篇:mysql之 mysql 5.6不停机双主一从搭建(活跃双主一从基于日志点复制)


下一篇:配置IP