mysql主从复制搭建
一:环境
主服务器
IP:192.168.1.205
系统:Ubuntu 16.04
MySQL:5.7.20
从服务器
IP:192.168.1.197
系统:Ubuntu 16.04
MySQL:5.7.20
mysql主从复制要求版本最好一致,至少前两个版本号相同,因为官方推荐这样,最主要还是怕版本不一致带来的不兼容问题;如果mysql版本不一致,建议让低版本作为主master,高版本作为从slave。
二:查看mysql版本
登录mysql,执行命令select version()
mysql> select version();
+-----------------------------+
| version() |
+-----------------------------+
| 5.7.20-0ubuntu0.16.04.1-log |
+-----------------------------+
1 row in set (0.00 sec)
三:在主服务器数据库创建从数据库账户
先登录主机 A,在主服务器上,设置一个从数据库的账户,使用REPLICATION SLAVE(从复制)赋予权限,如:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.1.197' IDENTIFIED BY '123456';
mysql> flush privileges;
赋予从机权限,有多台从机,就执行多次。
四:主服务器数据库配置
打开主机A的my.cnf,输入如下:(修改主数据库的配置文件my.cnf,开启BINLOG,并设置server-id的值,修改之后必须重启mysql服务)
...
# bind-address = 127.0.0.1 #这里默认监听本地localhost
server-id = 1 #主机标示,整数
log_bin = /var/log/mysql/mysql-bin.log #确保此文件可写,开启bin-log
read-only =0 #主机,读写都可以
binlog-do-db =test #指定mysql的binlog日志记录哪个db,多个写多行
binlog-ignore-db =mysql #不需要备份的数据库,多个写多行
...
注意:如果要让mysql监听到其他的地址,可以将bind-address = 127.0.0.1注释掉。
或者将bind-address = 0.0.0.0监听所有的地址
1. 如果不按照如上所述设置,会报出错误信息:MYSQL ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.1.X’ (111)
2. 如果按照如上所述设置,报出错误信息:Host ‘192.168.1.X’ is not allowed to connect to this MySQL server ,那么请检查数据库账号的权限是否有问题。
重启mysql服务(必须)
sudo service mysql restart
可以通过以下命令验证二进制日志是否已经启动
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/log/mysql/mysql-bin |
| log_bin_index | /var/log/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | /var/log/mysql/error.log |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | OFF |
| log_syslog_facility | daemon |
| log_syslog_include_pid | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+--------------------------------+
21 rows in set (0.01 sec)
五:备份主服务器数据库
现在可以停止主数据的的更新操作,并生成主数据库的备份,我们可以通过mysqldump到处数据到从数据库,当然了,你也可以直接用cp命令将数据文件复制到从数据库去,注意在导出数据之前先对主数据库进行READ LOCK,以保证数据的一致性
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
然后mysqldump导出数据(注意此命令不是在mysql中执行):
mysqldump -h127.0.0.1 -p3306 -uroot -p test > /data/backup/test.sql
得到主服务器当前二进制日志名和偏移量,这个操作的目的是为了在从数据库启动后,从这个点开始进行数据的恢复。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 | 517 | test | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
最好在主数据库备份完毕,恢复写操作。
mysql> unlock tables;
Query OK, 0 rows affected (0.28 sec)
六:从服务器还原数据库
将刚才主数据备份的test.sql复制到从数据库(navicat、phpmyadmin、命令行都可以),进行导入(注意此命令不是在mysql中执行):
mysql -h localhost -u root -p test <e:\mysql\test.sql
七:从服务器数据库配置
配置主服务器ip及登录账号:
mysql> CHANGE MASTER TO
> MASTER_HOST='192.168.1.205',
> MASTER_USER='backup',
> MASTER_PASSWORD='123456',
> MASTER_LOG_FILE='mysql-bin.000007',
> MASTER_LOG_POS=517;
修改从数据库的my.cnf,增加server-id参数,指定复制使用的用户,主数据库服务器的ip,端口以及开始执行复制日志的文件和位置。打开从机B的my.cnf,输入(修改之后必须重启mysql服务)
···
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
replicate-do-db =test #只复制某个库
replicate-ignore-db=mysql #不复制某个库
···
重启mysql服务(必须)
sudo service mysql restart
在从服务器上,启动slave进程
mysql> start slave;
在从服务器进行show salve status验证
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.X
Master_User: root
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 263
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql
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: 263
Relay_Log_Space: 564
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:
1 row in set (0.00 sec)
提示如下信息,说明配置成功了。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果出现Slave_SQL_Running:No
解决方案一:
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的
解决办法:
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;解决方案二:
首先停掉Slave服务:slave stop
到主服务器上查看主机状态:
记录File和Position对应的值
进入master
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 | | |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
然后到slave服务器上执行手动同步:
mysql> change master to
master_host='master_ip',
master_user='user',
master_password='pwd',
master_port=3306,
master_log_file=localhost-bin.000094',
master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> start slave ;
1 row in set (0.00 sec)
手动同步需要停止master的写操作!
八:测试主从服务器是否能同步
自行增删改数据,测试。
九:mysql binlog日志查看
mysql> show binlog events\G;
*************************** 12. row ***************************
Log_name: mysql-bin.000007
Pos: 985
Event_type: Query
Server_id: 1
End_log_pos: 1075
Info: use `test`; delete from user where id=2
*************************** 13. row ***************************
Log_name: mysql-bin.000007
Pos: 1075
Event_type: Query
Server_id: 1
End_log_pos: 1175
Info: use `test`; alter table user add name varchar(50)
*************************** 14. row ***************************
Log_name: mysql-bin.000007
Pos: 1175
Event_type: Query
Server_id: 1
End_log_pos: 1287
Info: use `test`; ALTER TABLE user MODIFY COLUMN name VARCHAR(200)
*************************** 15. row ***************************
Log_name: mysql-bin.000007
Pos: 1287
Event_type: Query
Server_id: 1
End_log_pos: 1376
Info: use `test`; create table user2(id int)
15 rows in set (0.00 sec)
十:参考文档:
http://www.linuxidc.com/Linux/2016-09/135121.htm
https://www.cnblogs.com/zihanxing/p/7049244.html
http://blog.csdn.net/heng_ji/article/details/51013710
http://blog.csdn.net/wulantian/article/details/9965905