最近搭了个主从复制,中间出了点小问题,排查搞定,记录下来
1
环境:
虚拟机:
OS:
centos6.5
Linux host2 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
服务器IP
192.168.18.66
192.168.18.67
DB:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.20 |
+-----------+
2
主机:192.168.18.66
从机:192.168.18.67
3
修改主服务器配置,添加如下内容:
server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest
此时主服务器这个配置文件/etc/my.cnf内容如下:
[client]
#password = system
#port = 3306
default-character-set=utf8
[mysqld]
server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500
[mysql]
default-character-set=utf8
4
修改从服务器配置
server-id=20
relay_log=mysql-relay-bin
read_only
此时从服务器配置文件内容如下:
[client]
#password=system
#port=3306
default-character-set=utf8
[mysqld]
server-id=20
relay_log=mysql-relay-bin
#read_only
#log_slave_updates=1
#master-host=192.168.18.66
#master-user=repl
#master-password=123
#master-port=3306
#master-connect-retry=60
#replicate_do_db=reptest
#replicate_ignore_db=mysql,information_schema,performance_schema
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500
[mysql]
default-character-set=utf8
mysql复制的相关参数可参考下面的网页:
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html
5
在主上增加复制用户
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
flush privileges;
192.168.18.67是从服务器,就通过repl用户密码为空来同步复制
mysql> select host,user,Repl_slave_priv from mysql.user where user='repl';
+---------------+------+-----------------+
| host | user | Repl_slave_priv |
+---------------+------+-----------------+
| 192.168.18.67 | repl | Y |
+---------------+------+-----------------+
1 row in set (0.00 sec)
6
重启主从服务器:
停主,停从
mysqladmin -uroot shutdown -psystem
起从,起主
/etc/init.d/mysql start
[root@host3 ~]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS!
7
导出主数据库数据,取快照
1)锁主库
flush tables with read lock;
2)
这一步比较重要,要记住File和Position值,在起从服务器上的slave线程时备用
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 401
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
3)
[root@host3 ~]# mysqldump -uroot -p reptest --triggers --routines --events > /home/zxw/master_reptest.sql
顺便看一下,mysqldump的内容如下:
[root@host3 ~]# ll /home/zxw/
total 4
-rw-r--r--. 1 root root 1910 Aug 25 13:50 master_reptest.sql
[root@host3 ~]# nl /home/zxw/master_reptest.sql
1 -- MySQL dump 10.13 Distrib 5.6.20, for Linux (x86_64)
2 --
3 -- Host: localhost Database: reptest
4 -- ------------------------------------------------------
5 -- Server version 5.6.20-log
6 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
7 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
8 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
1
环境:
虚拟机:
OS:
centos6.5
Linux host2 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
服务器IP
192.168.18.66
192.168.18.67
DB:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.20 |
+-----------+
2
主机:192.168.18.66
从机:192.168.18.67
3
修改主服务器配置,添加如下内容:
server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest
此时主服务器这个配置文件/etc/my.cnf内容如下:
[client]
#password = system
#port = 3306
default-character-set=utf8
[mysqld]
server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500
[mysql]
default-character-set=utf8
4
修改从服务器配置
server-id=20
relay_log=mysql-relay-bin
read_only
此时从服务器配置文件内容如下:
[client]
#password=system
#port=3306
default-character-set=utf8
[mysqld]
server-id=20
relay_log=mysql-relay-bin
#read_only
#log_slave_updates=1
#master-host=192.168.18.66
#master-user=repl
#master-password=123
#master-port=3306
#master-connect-retry=60
#replicate_do_db=reptest
#replicate_ignore_db=mysql,information_schema,performance_schema
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500
[mysql]
default-character-set=utf8
mysql复制的相关参数可参考下面的网页:
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html
5
在主上增加复制用户
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
flush privileges;
192.168.18.67是从服务器,就通过repl用户密码为空来同步复制
mysql> select host,user,Repl_slave_priv from mysql.user where user='repl';
+---------------+------+-----------------+
| host | user | Repl_slave_priv |
+---------------+------+-----------------+
| 192.168.18.67 | repl | Y |
+---------------+------+-----------------+
1 row in set (0.00 sec)
6
重启主从服务器:
停主,停从
mysqladmin -uroot shutdown -psystem
起从,起主
/etc/init.d/mysql start
[root@host3 ~]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS!
7
导出主数据库数据,取快照
1)锁主库
flush tables with read lock;
2)
这一步比较重要,要记住File和Position值,在起从服务器上的slave线程时备用
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 401
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
3)
[root@host3 ~]# mysqldump -uroot -p reptest --triggers --routines --events > /home/zxw/master_reptest.sql
顺便看一下,mysqldump的内容如下:
[root@host3 ~]# ll /home/zxw/
total 4
-rw-r--r--. 1 root root 1910 Aug 25 13:50 master_reptest.sql
[root@host3 ~]# nl /home/zxw/master_reptest.sql
1 -- MySQL dump 10.13 Distrib 5.6.20, for Linux (x86_64)
2 --
3 -- Host: localhost Database: reptest
4 -- ------------------------------------------------------
5 -- Server version 5.6.20-log
6 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
7 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
8 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;