Mysql 主从同步
主:192.168.10.17
从:192.168.10.20
一、先修改主库的/etc/mysql/my.cnf
[mysqld]
server-id = 1 #master的标示 不能重复
log_bin = /var/log/mysql/mysql-bin.log #slave会基于此log-bin来做replication
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_format=row
修改从库/etc/mysql/my.cnf
server-id=2 #slave的标示 不能重复
分别重启两个数据库服务
sudo serivice mysql restart
二、创建主从复制的用户及相应的权限
mysql> GRANT REPLICATION SLAVE ON *.* to 'nothing'@'192.168.10.20' identified by '123456';
主库 查看主数据库状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
从库设置并启动主从机制
mysql> change master to master_host='192.168.10.31',
-> master_user='nothing',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=106;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
从库查看启动状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.17
Master_User: nothing
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 264
Relay_Log_File: ubuntu-relay-bin.000002
Relay_Log_Pos: 409
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 264
Relay_Log_Space: 565
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)
当出现以上状态时表示成功!如果不成功!后半段会报出相应的错误! 这时在主库创建的库、表、数据全部会自动同步到从库。
几点注意事项:
- 从库的有户连不上时注意主库中的账户的Host字段地址
- 注意/etc/mysql/my.cnf中 #bind-address = 127.0.0.1 注释掉,不然不能远程连。可以在从库服务器上用 mysql -h 192.168.10.17 -unothing -p123456来测试创建的用户是否可以连接上。
先到这里! 有问题欢迎留言!