mysql数据库主从复制
文章目录
mysql数据库主从复制1:简介2：主从形式3：主从复制原理4.1 确保主从数据库里的数据都要一样4.2在主数据库里创建一个同步账号授权给从数据库使用4.3 配置主数据库配置文件4.4配置从数据库配置文件4.5 关闭主从服务器的防火墙和selinux4.6 测试5:mysql多实例做主从复制6:mysql配置systemctl管理1:简介
数据库运行时，一些因素可能会导致服务运行不正常，用户访问数据受阻。对于互联网公司，尤其是购物网站而言，这种情况造成的损失是无法估量的。因此，对数据库进行“备份”也是必不可少的操作。当主要的数据库死机时，系统能够快速地切换到备用的数据库上。本章将详细介绍数据库集群中的主从复制原理和操作流程。
2：主从形式
一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制
3：主从复制原理
主从复制又被称为AB复制，主要用于实现数据库集群中的数据同步。实现MySQL的AB复制时，数据库的版本应尽量保持一致。
在主从复制集群中，主数据库把数据更改的操作记录到二进制日志中，从数据库分别启动I/O线程和SQL线程，用于将主数据库中的数据复制到从数据库中。其中，I/O线程主要将主数据库上的日志复制到自己的中继日志中，SQL线程主要用于读取中继日志中的事件，并将其重放到从数据库之上。另外，系统会将I/O线程已经读取的二进制日志的位置信息存储在master.info文件中，将SQL线程已经读取的中继日志的位置信息存储在relay-log.info文件中。随着版本的更新，在MySQL 5.6.2之后，MySQL允许将这些状态信息保存在Table中，不过在更新之前需要用户在配置文件中进行声明，具体的参数如下。
4：主从复制配置
步骤
1·确保从数据库与主数据库里的数据一样
2·在主数据库里创建一个同步账号授权给从数据库使用
3·配置主数据库（修改配置文件）
4·配置从数据库（修改配置文件）
环境说明
在实验开始之前要确保两台服务器上有mysql服务
4.1 确保主从数据库里的数据都要一样
查看有哪些库哪些表
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || nian|| performance_schema || sys|+--------------------+5 rows in set (0.01 sec)mysql>mysql> use nian;Database changedmysql> show tables;+----------------+| Tables_in_nian |+----------------+| xuan |+----------------+1 row in set (0.00 sec)mysql>
全备主数据库，并给数据库上锁
mysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)[root@master ~]# mysqldump -uroot -p123456789 --all-databases > all--07-29.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@master ~]# ls公共 模板 视频 图片 文档 下载 音乐 桌面 all--07-29.sql anaconda-ks.cfg initial-setup-ks.cfg[root@master ~]# scp all--07-29.sql root@192.168.226.158:/The authenticity of host '192.168.226.158 (192.168.226.158)' can't be established.ECDSA key fingerprint is SHA256:ruZxylllox/yy17nVIQPDXNcQoydqXbjt6rjkhTQgyE.Are you sure you want to continue connecting (yes/no/[fingerprint])? yesWarning: Permanently added '192.168.226.158' (ECDSA) to the list of known hosts.root@192.168.226.158's password:all--07-29.sql100% 856KB 65.8MB/s 00:00[root@master ~]#[root@clave /]# lsall--07-29.sql bin boot dev etc home lib lib64 media mnt opt proc root run sbin srv sys tmp usr var
解除主库的锁表状态，直接退出即可
mysql> exitBye
在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave /]# mysql -uroot -p123456789mysql> source all--07-29.sqlmysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || nian|| performance_schema || sys|+--------------------+5 rows in set (0.00 sec)mysql> use nian;Database changedmysql> show tables;+----------------+| Tables_in_nian |+----------------+| xuan |+----------------+1 row in set (0.00 sec)
4.2在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'xuanning'@'192.168.226.158' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO 'xuanning'@'192.168.226.158';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
4.3 配置主数据库配置文件
[root@master local]# vim /etc/flog-bin=gserver-id=2000[root@master data]# service mysqld restartShutting down MySQL.. SUCCESS!Starting MySQL. SUCCESS!mysql> show master status;+----------+----------+--------------+------------------+-------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+----------+----------+--------------+------------------+-------------------+| g.000002 |154 | | | |+----------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
4.4配置从数据库配置文件
[root@slave /]# vim /etc/flog-bin=jserver-id=2001[root@slave /]# service mysqld restartShutting down MySQL.. SUCCESS!Starting MySQL.Logging to '/opt/data/slave.err'.SUCCESS!mysql> change master to-> master_host='192.168.226.139' ,-> master_user = 'xuanning' ,-> master_password = '123456' ,-> master_log_file = 'g.000002' ,-> master_log_pos = 154;Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;Query OK, 0 rows affected (0.02 sec)mysql> show slave status \G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.226.139Master_User: xuanningMaster_Port: 3306Connect_Retry: 60Master_Log_File: g.000003Read_Master_Log_Pos: 154Relay_Log_File: slave-relay-bin.000007Relay_Log_Pos: 351Relay_Master_Log_File: g.000003Slave_IO_Running: Yes <<这里一定要是yesSlave_SQL_Running: Yes
4.5 关闭主从服务器的防火墙和selinux
[root@slave /]# systemctl stop firewalld.service[root@slave /]# vim /etc/selinux/configSELINUX=disable[root@slave /]# setenforce 0
4.6 测试
查看主从服务器表的内容
mysql> use nian;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from xuan;+----+----------+------+| id | name| age |+----+----------+------+| 1 | xuanning | 21 || 2 | nianxia | 22 || 3 | zhangsan | 23 || 4 | lisi| 24 |+----+----------+------+4 rows in set (0.00 sec)mysql> use nian;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from xuan;+----+----------+------+| id | name| age |+----+----------+------+| 1 | xuanning | 21 || 2 | nianxia | 22 || 3 | zhangsan | 23 || 4 | lisi| 24 |+----+----------+------+4 rows in set (0.00 sec)
在主服务器中插入数据
mysql> insert xuan(id,name,age) values (5,'zhaosi',30);Query OK, 1 row affected (0.01 sec)mysql> select * from xuan;+----+----------+------+| id | name| age |+----+----------+------+| 1 | xuanning | 21 || 2 | nianxia | 22 || 3 | zhangsan | 23 || 4 | lisi| 24 || 5 | zhaosi | 30 |+----+----------+------+5 rows in set (0.00 sec)
在从数据库中查看是否同步过来
mysql> select * from xuan;+----+----------+------+| id | name| age |+----+----------+------+| 1 | xuanning | 21 || 2 | nianxia | 22 || 3 | zhangsan | 23 || 4 | lisi| 24 || 5 | zhaosi | 30 |+----+----------+------+5 rows in set (0.00 sec)
5:mysql多实例做主从复制
在主数据库中创建远程连接用户用作同步
mysql> create user 'xuanning'@'192.168.226.158' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO 'xuanning'@'192.168.226.158';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
关闭防火墙和selinux
[root@slave /]# systemctl stop firewalld.service[root@slave /]# vim /etc/selinux/configSELINUX=disable[root@slave /]# setenforce 0
配置本地文件开启二进制日志并设置服务id主库id一定要比从库小
[mysqld3306]datadir = /opt/data/3306port = 3306socket = /tmp/mysql3306.sockpid-file = /opt/data/3306/mysql_3306.pidlog-error=/var/log/3306.loglog-bin=gserver-id=2000[mysqld3307]datadir = /opt/data/3307port = 3307socket = /tmp/mysql3307.sockpid-file = /opt/data/3307/mysql_3307.pidlog-error=/var/log/3307.loglog-bin=oserver-id=2001
进入数据库连接
mysql> change master to-> master_host='192.168.226.139' ,-> master_user='xuanning' ,-> master_password='123456' ,-> master_log_file='g.000001' ,-> master_log_pos=154 ,-> master_port=3306; //端口Query OK, 0 rows affected, 2 warnings (0.02 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 eventMaster_Host: 192.168.226.139Master_User: xuanningMaster_Port: 3306Connect_Retry: 60Master_Log_File: g.000001Read_Master_Log_Pos: 154Relay_Log_File: localhost-relay-bin.000002Relay_Log_Pos: 312Relay_Master_Log_File: g.000001Slave_IO_Running: Yes //此处一定要是yesSlave_SQL_Running: YesReplicate_Do_DB:
查看主数据库库与从数据库里面的数据
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3306.sockmysql: [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 3Server version: 5.7.37-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys|+--------------------+4 rows in set (0.00 sec)mysql> exitBye[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3307.sockmysql: [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 5Server version: 5.7.37-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys|+--------------------+4 rows in set (0.00 sec)mysql>
测试
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3306.sockmysql: [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 4Server version: 5.7.37-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database nian;Query OK, 1 row affected (0.01 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || nian|| performance_schema || sys|+--------------------+5 rows in set (0.00 sec)mysql> exitBye[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3307.sockmysql: [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 6Server version: 5.7.37-log MySQL Community Server (GPL)Copyright (c) 2000, , Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || nian|| performance_schema || sys|+--------------------+5 rows in set (0.00 sec)mysql>
6:mysql配置systemctl管理
写入配置文件,关闭mysql服务
# vim /usr/lib/systemd/system/mysql.service[Unit]Description= mysql server daemonAfter=network.target[Service]Type=forkingExecStart=/usr/local/mysql/bin/mysqld_multi start mysqlExecStop=ps -ef | grep mysql | grep -v grep | awk '{print$2}' | xargs kill -9ExecReload=/bin/kill -HUP $MAINPID[Install]WantedBy=muyi-user.target[root@localhost local]# systemctl stop mysqld.service[root@localhost local]# ss -antlState Recv-Q Send-QLocal Address:PortPeer Address:Port ProcessLISTEN 0 128 0.0.0.0:111 0.0.0.0:*LISTEN 0 32 192.168.122.1:53 0.0.0.0:*LISTEN 0 128 0.0.0.0:22 0.0.0.0:*LISTEN 0 5 127.0.0.1:631 0.0.0.0:*LISTEN 0 128 127.0.0.1:60100.0.0.0:*LISTEN 0 128 [::]:111 [::]:*LISTEN 0 128 [::]:22[::]:*LISTEN 0 5 [::1]:631 [::]:*LISTEN 0 128[::1]:6010 [::]:*
