MySQL主从复制
| 主机名称 | IP | 备注 |
|---|---|---|
| mysql-master | 192.168.175.93 | master |
| mysql-slave | 192.168.175.130 | slave |
- 在mysql-master上创建一个复制账号；
- mysql-master和mysql-slave上分别设置不同的server_id；
- 把主库现有数据备份下来，再恢复到从库；
- mysql-slave上执行change master设置主从复制；
- mysql-slave上执行start slave启动复制；
- 验证 。
step 1 在mysql-master库上创建一个复制用户。
mysql> grant replication slave on *.* to 'rep'@'%' identified by 'C#ssw0rd';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 检查创建的rep账号：
mysql> select user,host from mysql.user;
step 2 修改mysql-master和mysql-slave上的server_id，确保不一样。
mysql-master@ubuntu:~# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep server-id
server-id = 93
mysql-slave@ubuntu:~# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep server-id
server-id = 130
step 3 把主库现有数据备份下来，再恢复到从库，此时两个主机的数据一致。
step 4 设置mysql-slave库是源库的从数据库
master 查看mysql-bin
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 239543 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
slave执行
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.175.130',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='C#ssw0rd',
MASTER_LOG_FILE='mysql-bin.000008',
MASTER_LOG_POS=239543;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
step 4 验证
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.175.93
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: ubuntu-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
..........
..........
..........
1 row in set (0.00 sec)
主库创建数据库、表、插入语句，发现在从库可以查询，验证完毕。
备注
在主库上加锁，使只有只读权限。
mysql> flush table with read lock;
解锁库：
mysql> unlock tables;