主从复制
主从复制实验
| 服务器 | IP | 端口 | 配置文件 |
| 主 | 192.168.6.146 | 3306 | /data/mysql/data |
| 从 | 192.168.6.146 | 3307 | /data/multi-mysql/3307 |
| 从 | 192.168.6.146 | 3308 | /data/multi-mysql/3308 |
0.启动服务
# ss -tlnp|grep 330
LISTEN 0 80 :::3306 :::* users:(("mysqld",1986,13))
# mysqld_safe --defaults-file=/data/multi-mysql/3307/my.cnf >/dev/null 2>&1 &
[2] 2557
!ss
# mysqld_safe --defaults-file=/data/multi-mysql/3308/my.cnf >/dev/null 2>&1 &
[2] 2715
# ss -tlnp|grep 330
LISTEN 0 80 :::3306 :::* users:(("mysqld",1986,13))
LISTEN 0 80 :::3307 :::* users:(("mysqld",2534,14))
LISTEN 0 80 :::3308 :::* users:(("mysqld",2875,10))
1.3306主库 cnf配置文件启动binlog日志记录
# mysql -uroot -p
Enter password:
mysql> show variables like 'log_bin'；
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
# vim /usr/local/mysql/my.cnf
[mysqld]
log_bin=/data/mysql/mysql-bin
server_id=149
mkdir -p /data/mysql/myssql-bin
# mysqladmin -uroot -p -S /usr/local/mysql/mysql.sock shutdown
Enter password:
# mysqld_safe --defaults-file=/usr/local/mysql/my.cnf > /dev/null 2>&1 &
# mysql -uroot -p -P 3306 --protocol tcp
Enter password:
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
2.3306主库创建用于主从复制的用户
mysql> grant replication slave on *.* to 'plrep'@'%' identified by '1234567';
flush privileges;
3.3306主库进行数据库全量备份
mysql>flush table with read lock;
mysql> exit;
# mysqldump -uroot -p -S /usr/local/mysql/mysql.sock -B school_utf8 > /data/multi-mysql/master2010241251.sql
Enter password:
mysql>unlock tables;
4.3306查看主库信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 319 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 319 |
+------------------+-----------+
--------------------------
5.从库3307全量恢复
# mysql -u root -p -S /data/multi-mysql/3307/mysql.sock
Enter password:
mysql> source /data/multi-mysql/master2010241251.sql;
mysql> show tables;
+-----------------------+
| Tables_in_school_utf8 |
+-----------------------+
| student |
+-----------------------+
6.从库3307 设定主库
mysql> change master to \
-> master_host='localhost',
-> master_port=3306,
-> master_user='plrep',
-> master_password='1234567',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=319;
7.从库3307，3308开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: plrep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1062
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 1026
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: 1062
Relay_Log_Space: 1199
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 149
Master_UUID: 61bfaa91-0eff-11eb-8f52-000c29dd0064
Master_Info_File: /data/multi-mysql/3307/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
----------------------------
8.测试
启动备份：
3306插入数据
mysql> use school_utf8;
mysql> select * from student;
+----+-------+-----+---------+
| id | name | age | dept |
+----+-------+-----+---------+
| 1 | alice | 30 | cs |
| 2 | ali | 20 | cs |
| 4 | bob | 22 | net |
| 5 | ali | 10 | math |
| 7 | bo | 21 | chinese |
| 8 | julia | 24 | english |
| 10 | mimi | 22 | 汉 |
| 12 | miki | 12 | ? |
| 14 | milo | 13 | ??? |
| 15 | molo | 13 | ? |
| 16 | mo | 33 | 闽 |
+----+-------+-----+---------+
mysql> insert into student values(21,'lilili',55,'math'),(23,'lololo',22,'net');
mysql> select * from student;
+----+--------+-----+---------+
| id | name | age | dept |
+----+--------+-----+---------+
| 1 | alice | 30 | cs |
| 2 | ali | 20 | cs |
| 4 | bob | 22 | net |
| 5 | ali | 10 | math |
| 7 | bo | 21 | chinese |
| 8 | julia | 24 | english |
| 10 | mimi | 22 | 汉 |
| 12 | miki | 12 | ? |
| 14 | milo | 13 | ??? |
| 15 | molo | 13 | ? |
| 16 | mo | 33 | 闽 |
| 21 | lilili | 55 | math |
| 23 | lololo | 22 | net |
+----+--------+-----+---------+
3307查看
mysql> use school_utf8;
mysql> select * from student;
+----+--------+-----+---------+
| id | name | age | dept |
+----+--------+-----+---------+
| 1 | alice | 30 | cs |
| 2 | ali | 20 | cs |
| 4 | bob | 22 | net |
| 5 | ali | 10 | math |
| 7 | bo | 21 | chinese |
| 8 | julia | 24 | english |
| 10 | mimi | 22 | 汉 |
| 12 | miki | 12 | ? |
| 14 | milo | 13 | ??? |
| 15 | molo | 13 | ? |
| 16 | mo | 33 | 闽 |
| 21 | lilili | 55 | math |
| 23 | lololo | 22 | net |
+----+--------+-----+---------+
----------------------------------
取消备份：
3307
mysql> stop slave;//就不再更新
3306
mysql> update student set dept='mse' where dept='?';
mysql> delete from student where id=1 or id=2;
mysql> select * from student;
+----+--------+-----+---------+
| id | name | age | dept |
+----+--------+-----+---------+
| 4 | bob | 22 | net |
| 5 | ali | 10 | math |
| 7 | bo | 21 | chinese |
| 8 | julia | 24 | english |
| 10 | mimi | 22 | 汉 |
| 12 | miki | 12 | mse |
| 14 | milo | 13 | ??? |
| 15 | molo | 13 | mse |
| 16 | mo | 33 | 闽 |
| 21 | lilili | 55 | math |
| 23 | lololo | 22 | net |
+----+--------+-----+---------+
3307
mysql> select * from student;
+----+--------+-----+---------+
| id | name | age | dept |
+----+--------+-----+---------+
| 1 | alice | 30 | cs |
| 2 | ali | 20 | cs |
| 4 | bob | 22 | net |
| 5 | ali | 10 | math |
| 7 | bo | 21 | chinese |
| 8 | julia | 24 | english |
| 10 | mimi | 22 | 汉 |
| 12 | miki | 12 | mse |
| 14 | milo | 13 | ??? |
| 15 | molo | 13 | mse |
| 16 | mo | 33 | 闽 |
| 21 | lilili | 55 | math |
| 23 | lololo | 22 | net |
+----+--------+-----+---------+
没有更新 所以万一主库有些数据误删了，该可以从从库里找回来，前提是关掉slave；
继续：
mysql> start slave;
mysql> select * from student;
+----+--------+-----+---------+
| id | name | age | dept |
+----+--------+-----+---------+
| 4 | bob | 22 | net |
| 5 | ali | 10 | math |
| 7 | bo | 21 | chinese |
| 8 | julia | 24 | english |
| 10 | mimi | 22 | 汉 |
| 12 | miki | 12 | mse |
| 14 | milo | 13 | ??? |
| 15 | molo | 13 | mse |
| 16 | mo | 33 | 闽 |
| 21 | lilili | 55 | math |
| 23 | lololo | 22 | net |
+----+--------+-----+---------+
![Linux云计算运维架构师（连载）-K8S 日志方案[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)