MySQL主从复制

MySQL主从复制

主机名称	IP	备注
mysql-master	192.168.175.93	master
mysql-slave	192.168.175.130	slave

---

1. 在mysql-master上创建一个复制账号；
2. mysql-master和mysql-slave上分别设置不同的server_id；
3. 把主库现有数据备份下来，再恢复到从库；
4. mysql-slave上执行change master设置主从复制；
5. mysql-slave上执行start slave启动复制；
6. 验证 。

---

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;