本节将如何设置 MySQL 的主从同步。
简单讲分三个环节:
具体操作日下:
编辑 /etc/mysql/my.cnf 文件,增加如下设置:
[mysqld]
log-bin=mysql-bin
server-id=1
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
编辑 /etc/mysql/my.cnf 文件,增加如下设置:
[mysqld]
server-id=2
使用 change master 指令进行参数设置,示例:
mysql> change master to master_host='192.168.1.8', master_user='repl', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=747;
设置好后,检查一下:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.8
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 747
Relay_Log_File: 59b3f32743e5-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
使用 指令,查看 Position 的值,示例如下:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 747 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
使用 start slave 指令启动即可。
mysql> start slave
至此,配置完毕。
修改完主数据库配置后,可以检查下配置是否生效 (对应上文步骤1)
mysql> show variables like 'log%'
-> ;
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
mysql> show variables like 'server%';
+----------------+--------------------------------------+
| Variable_name | Value |
+----------------+--------------------------------------+
| server_id | 1 |
| server_id_bits | 32 |
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 747 | | | |
+------------------+----------+--------------+------------------+-------------------+
以 docker 方式启动 mysql
docker run -di --name mysql_copy -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
docker 里没有 vi编辑器 怎么办? 安装一个
apt-get update
apt-get install vim
查看进程列表:
mysql> show processlist;
# 比如在从数据库看到:
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 5 | root | localhost | zoo | Query | 0 | starting | show processlist |
| 6 | system user | | NULL | Connect | 740 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 593 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
备份数据库时,锁定数据表和解锁的方法 在服务器上,设定一个锁,确保没有操作数据库,以便获得一致性。
FLUSH TABLES WITH READ LOCK;
主库备份完成后,恢复写操作
UNLOCK TABLES;
https://www.cnblogs.com/cjsblog/archive/2018/09/26/9706370.html