MySQL主从
- 2021 年 1 月 2 日
- 笔记
主从形式
原理：
（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致。
主从复制配置
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
主数据库：IP192.168.248.130 无数据
从数据库：IP192.168.248.131 无数据
关闭主从防火墙和selinux
[root@slave ~]# systemctl stop firewalld [root@slave ~]# setenforce 0
查看主从数据库又那些库
#主库 [root@master ~]# mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ #从库 [root@slave ~]# mysql -uroot -p123456 -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
在主数据库里创建一个同步账号授权给从数据库使用
#主库创建同步账号 mysql> grant replication slave on *.* to 'test'@'192.168.248.131' identified by 'test123'； #在从库登录测试 [root@slave ~]# mysql -utest -ptest123 -h192.168.248.130 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
配置主库数据库
#配置主库数据库 [mysqld] #在mysqld下 ............... ............... server-id = 10 # 添加启用binlog日志 log-bin = mysql_bin #添加据库服务器唯一标识符，主库的server-id值必须比从库的小 #重启mysql [root@localhost ~]# service mysqld start Starting MySQL. SUCCESS! #查看主库状态 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000001 | 451 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)
配置从库数据库
#配置从库数据库
[root@slave ~]# vim /etc/my.cnf
[mysqld]
............
............
server-id = 20
relay-log = mysql_relay_bin
#重启mysql
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.Logging to '/opt/data/slave.err'.
SUCCESS!
#配置并启动主从复制
mysql> change master to
-> master_host='192.168.248.130',
-> master_user='test',
-> master_password='test123',
-> master_log_file='mysql_bin.000001',
-> master_log_POS=451;
Query OK, 0 rows affected, 2 warnings (0.01 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.248.130
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 451
Relay_Log_File: mysql_relay_bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes #看到两个yes表示成功
Slave_SQL_Running: Yes
测试验证
在主库中写入数据
#在主库中创建表写入数据
mysql> create database t1;
Query OK, 1 row affected (0.01 sec)
mysql> use t1;
Database changed
mysql> create table test(id int not null auto_increment,name varchar(20),age tinyint,primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> insert test(name,age) values('xx',34),('ww',24),('gg',25);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | xx | 34 |
| 2 | ww | 24 |
| 3 | gg | 25 |
+----+------+------+
3 rows in set (0.00 sec)
在从库中查看数据是否同步
[root@slave ~]# mysql -uroot -p123456 -e 'select * from t1.test;' mysql: [Warning] Using a password on the command line interface can be insecure. +----+------+------+ | id | name | age | +----+------+------+ | 1 | xx | 34 | | 2 | ww | 24 | | 3 | gg | 25 | +----+------+------+
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
主数据库：IP192.168.248.130 有数据
从数据库：IP192.168.248.131 无数据
主库上有数据
[root@localhost ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | jobs | | mysql | | performance_schema | | sys | +--------------------+ [root@master ~]# mysql -uroot -p -e 'select * from jobs.student;' Enter password: +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 12 | | 2 | natasha | 12 | | 3 | harry | 11 | +----+---------+------+
从库上无数据
[root@slave ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
全备主库
#全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致 mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.01 sec [root@master ~]# mysqldump -uroot -p123456 --all-databases > all_$(date +%F_%T).sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@master ~]# ls all_2021-01-02_18:34:26.sql #将备份数据推送至从服务器 [root@master ~]# scp /root/all_2021-01-02_18\:34\:26.sql 'root'@192.168.248.131:/root/ [email protected]'s password: all_2021-01-02_18:34:26.sql 100% 843KB 87.2MB/s 00:00 #同步从库与主库，查看数据 [root@slave ~]# mysql -uroot -p <all_2021-01-02_18\:34\:26.sql Enter password: [root@slave ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | jobs | | mysql | | performance_schema | | sys | +--------------------+ [root@slave ~]# mysql -uroot -p -e 'select * from jobs.student;' Enter password: +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | alice | 12 | | 2 | natasha | 12 | | 3 | harry | 11 | +----+---------+------+ #退出读锁终端
后面就和主从无数据配置一样
