MySQL主从复制
标签(空格分隔): mysql
其他不错的文档:https://www.cnblogs.com/kevingrace/p/6256603.html
以相同版本参照《MySQL源代码安装》分别安装MySQL两台服务器
进入主机MySQL
$ cd $MYSQL_HOME
$ bin/mysql -uroot -p
- 创建slave帐号slave_account,密码Xianzai@2602
mysql>grant replication slave on *.* to 'slave_account'@'%' identified by 'Xianzai@2602';
mysql>flush privileges;
- 修改主机my.cnf
server_id=1
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
# 只同步哪些数据库,除此之外,其他不同步
# binlog-do-db = urmp
# 日志保留时间
expire_logs_days = 30
# 控制binlog的写入频率。每执行多少次事务写入一次
# 这个参数性能消耗很大,但可减小MySQL崩溃造成的损失
sync_binlog = 5
- 重启MySQL后,重新进入,查看master状态
$ bin/mysqladmin -uroot -p shutdown
$ bin/mysqld_safe --defaults-file=/home/urmp/libs/mysql/my.cnf
$ bin/mysql -uroot -p
mysql>show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000008 | 120 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
- 进入从机MySQL
- 修改my.cnf
read_only=1 #设置从机只读
server_id=2
- 重启MySQL,开启同步
# 执行同步命令,设置主数据库ip,同步帐号密码,同步位置
mysql>change master to master_host='10.92.82.34',master_user='slave_account',master_password='Xianzai@2602',master_log_file='mysql-bin.000008',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: 10.92.82.34
Master_User: slave_account
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
………………………………………………………………………………………………
注:Slave_IO_Running及Slave_SQL_Running进程必须正常运行,即YES状态,否则说明同步失败。
Slave_IO_Running: Connecting 的原因:
1、网络不通
2、密码不对
3、Position不对
Slave_SQL_Running: No 的原因:
1.程序可能在slave上进行了写操作
2.可能是slave机器重起后,事务回滚造成的
解决办法:
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;