MySQL 5.6.33 主从复制与主主复制


1 实验环境

# cat /etc/RedHat-release 

CentOS Linux release 7.2.1511 (Core) 

A主机：master IP：192.168.1.138 MYSQL:# mysql -Vmysql  Ver 14.14 Distrib 5.6.33

B主机：slave  IP：192.168.1.9   MYSQL:# mysql -Vmysql  Ver 14.14 Distrib 5.6.33

注意:mysql数据库的版本，两个数据库版本要相同，或者slave比master版本低！

2 mysql5.6.33 安装：

1 下载mysql的rpm安装包

[root@localhost mysql]# ls MySQL-server-5.6.33-1.el6.x86_64.rpm MySQL-client-5.6.33-1.el6.x86_64.rpm    MySQL-devel-5.6.33-1.el6.x86_64.rpm

2 检查MySQL及相关RPM包，是否安装，如果有安装，则移除（rpm –e 名称）

[root@localhost mysql]# rpm -qa | grep -i mysql [root@localhost mysql]#  [root@localhost mysql]# rpm -ivh MySQL-server-5.6.33-1.el6.x86_64.rpm  warning: MySQL-server-5.6.33-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY error: Failed dependencies: net-tools is needed by MySQL-server-5.6.33-1.el6.x86_64 [root@localhost mysql]# yum -y install net-tools [root@localhost mysql]# rpm -ivh MySQL-server-5.6.33-1.el6.x86_64.rpm file /usr/share/mysql/charsets/swe7.xml from install of MySQL-server-5.6.33-1.el6.x86_64 conflicts with file from package mariadb-libs-1:5.5.44-2.el7.centos.x86_64     如果报错，就是删除mariadb的包)   [root@localhost mysql]# yum remove mariadb-libs [root@localhost mysql]# rpm -ivh MySQL-server-5.6.33-1.el6.x86_64.rpm  [root@localhost mysql]# rpm -ivh MySQL-devel-5.6.33-1.el6.x86_64.rpm [root@localhost mysql]# rpm -ivh MySQL-client-5.6.33-1.el6.x86_64.rpm  [root@localhost mysql]# cp /usr/share/mysql/my-default.cnf /etc/my.cnf [root@localhost mysql]# mkdir /mysqldata [root@localhost mysql]# chown mysql:mysql /mysqldata [root@localhost mysql]# /usr/bin/mysql_install_db [root@localhost mysql]# cat /root/.mysql_secret  # The random password set for the root user at Tue Mar 21 01:12:42 2017 (local time): a64DA4mpLbeCaydf (5.6安装的默认密码不在是空，而是随机密码，自己要修改) root@localhost mysql]# /etc/init.d/mysql restart [root@localhost mysql]# mysql -uroot -pa64DA4mpLbeCaydf mysql> set password = password('123456'); 修改root的密码 Query OK, 0 rows affected (0.00 sec) mysql> exit [root@localhost mysql]# mysql -uroot -p123456

2.1 修改数据的data目录，配置服务器的my.cnf文件：（如果需要修改datadir，请注意SELinux和目录是否有mysql用户的权限）

/var/lib/mysql/               #数据库目录 /usr/share/mysql              #配置文件目录 /usr/bin                     #相关命令目录 /etc/init.d/mysql              #启动脚本     #mkdir -p /mysql/data #chown -R mysql:mysql  /mysql/data #cp -ar /var/lib/mysql/* /mysql/data/ #vim /etc/my.cnf datadir = /mysqldata/data port = 3306 socket = /mysqldata/data/mysql.sock  [root@localhost mysql]# /etc/init.d/mysql restart Shutting down MySQL.. SUCCESS!  Starting MySQL. SUCCESS!  改了datadir和socket，重启成功，但是连接报错 [root@localhost mysql]# mysql -uroot -p Enter password:  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) [root@localhost mysql]# ss -tnlp | grep 3306 LISTEN     0      80          :::3306                    :::*                   users:(("mysqld",pid=9413,fd=10)) 这个时候在配置文件中加以下配置： [client] socket = /mysqldata/data/mysql.sock [root@localhost mysqldata]# /etc/init.d/mysql restart Shutting down MySQL.. SUCCESS!  Starting MySQL. SUCCESS!

 

3 在主服务器为从服务器设置一个连接账户，该账号必须授予REPLICATION SLAVE权限。

如果账户仅用于复制(推荐这样做),则不需要再授予其它任何权限。

# mysql -uroot -p123456 mysql> grant replication slave on *.* to 'user'@'slave_ip' identified by '123456'; #mysql>grant file on *.* to 'user'@'slave_ip' IDENTIFIED BY 'mysql password'; mysql>flush privileges; mysql> show  databases; +--------------------+ | Database           | +--------------------+ | information_schema | | apacex             | | mysql              | | performance_schema | | test               | +--------------------+ 

创建一个测试数据库test1：

mysql> create database test1; mysql> use test1; mysql> create table users(id int NOT NULL,name VARCHAR(100) NOT NULL); mysql> insert into users values(1,"user1"); mysql> insert into users values(2,"user3"); mysql> flush privileges; mysql> select * from users; +----+-------+ | id | name  | +----+-------+ |  1 | user1 | |  2 | user3 | +----+-------+ 

3.1 编辑主配置文件my.cnf

server_id = 1  #server-id用于标识唯一的数据库，这里设置为1 character_set_server = utf8  max_connections = 3000 skip-name-resolve slow_query_log = TRUE slow_query_log_file = /home/db/slow.log long_query_time = 5 log-bin = mysql-bin binlog_format = ROW binlog-do-db =  xxx  ##可以被从服务器复制的库。 # binlog-ignore-db = yyy 不可以被从服务器复制的库 innodb_file_per_table = OFF open_files_limit = 65535 back_log = 600 max_connect_errors = 6000 sql_mode = NO_ENGINE_SUBSTITUTION auto_increment_increment = 2 auto_increment_offset = 1       # touch /mysql/data/slow.log # chown mysql:mysql /mysql/data/slow.log [root@localhost data]# /etc/init.d/mysql restart Shutting down MySQL.. SUCCESS!  Starting MySQL. SUCCESS!  登陆mysql，查看主的状态； mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      120 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+

 

4 配置从服务器：

vim /etc/my.cnf character_set_server = utf8 max_connections = 3000 skip-name-resolve slow_query_log = TRUE slow_query_log_file = /home/db/slow.log long_query_time = 5 log-bin = mysql-bin binlog_format = ROW innodb_file_per_table = OFF open_files_limit = 65535 back_log = 600 max_connect_errors = 6000 server_id = 2 sql_mode = NO_ENGINE_SUBSTITUTION auto_increment_increment = 2 auto_increment_offset = 2 replicate-do-db = test1 要接收的库     # /etc/init.d/mysql restart Shutting down MySQL.. SUCCESS!  Starting MySQL..^[[A. SUCCESS! # mysql -uroot -p123456 change master to master_host='192.168.1.28',master_user='root',master_password='abc-123',master_log_file='mysql-bin.000001', master_log_pos=120;     mysql> start slave;  #开启Slave mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.28                   Master_User: hm                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000004           Read_Master_Log_Pos: 120                Relay_Log_File: localhost-relay-bin.000013                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000004              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB: students           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: 460               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: 71a3a3a2-0d99-11e7-b0de-000c290c49b2              Master_Info_File: /mysql/data/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.13 sec)

5 测试：

主：

mysql> use students; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into users values (3,"c"); Query OK, 1 row affected (0.01 sec) mysql> select  * from users; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | +----+------+ 3 rows in set (0.00 sec) 

看从库：

mysql> use students; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from users; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | +----+------+ 3 rows in set (0.00 sec) 

到此为止主从就做好了，现开始做主主了：

1 在原从服务器上授权：

mysql> grant replication slave on *.* to 'hm100'@'192.168.1.28' identified by '123456'; Query OK, 0 rows affected (0.03 sec) 

1.1 编辑配置文件

binlog-do-db = students 加要同步的数据库 

1.2 重启

# /etc/init.d/mysql restart Shutting down MySQL... SUCCESS!  Starting MySQL..... SUCCESS!  mysql -uroot -p mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 |      120 | students     |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

 

2 在原主上添加：

vim /etc/my.cnf  添加： replicate-do-db = xxx 要接收的库 # /etc/init.d/mysql restart Shutting down MySQL... SUCCESS!  Starting MySQL..... SUCCESS!     mysql -uroot -p mysql> change master to master_host='192.168.1.166',master_user='hm100',master_password='abc-123',master_log_file='mysql-bin.000007', master_log_pos=120; mysql> start slave; mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.166                   Master_User: hm100                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000007           Read_Master_Log_Pos: 120                Relay_Log_File: localhost-relay-bin.000004                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000007              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB: shudents           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  ...... 

3 测试：

在原从上添加数据：

mysql> use students; mysql> select * from users; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | +----+------+ mysql> use students; mysql> insert into users values (7,"g"); mysql> select * from users; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  7 | g    | +----+------+

看原主

mysql> select * from users; +----+------+ | id | name | +----+------+ |  1 | a    | |  2 | b    | |  3 | c    | |  7 | g    | +----+------+

到此就实现了主主。

注意：

在mysql5.6之后不用指定以下，不然会出现报错：

master-host=192.168.1.1 #Master的主机IP master-user=root master-password=mysql password #Master的MySQL密码 Shutting down MySQL... SUCCESS!  Starting MySQL... ERROR! The server quit without updating PID file (/data/mysqldb/xxx.pid)

相关内容