mysql主从
主从形式
原理：
（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致。
主从复制配置
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
主数据库：IP192.168.220.10 无数据
从数据库：IP192.168.220.20 无数据
//关闭防火墙和selinux [root@chouyu ~]# systemctl stop firewalld [root@chouyu ~]# setenforce 0
[root@chouyu ~]# systemctl start mariadb [root@chouyu ~]# mysql -uroot -p MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+
//创建同步账号
MariaDB [(none)]> grant replication slave on *.* to 'xixi'@'192.168.220.20' identified by 'xixi123' ;
Query OK, 0 rows affected (0.000 sec)
[root@chouyu ~]# mysql -uroot -p MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.000 sec) //登录测试 [root@chouyu ~]# mysql -uxixi -pxixi123 -h192.168.220.10 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.3.11-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#配置主库数据库 [root@chouyu ~]# vim /etc/my.cnf ... [mysqld] server-id = 10 # 添加启用binlog日志 log-bin = mysql_bin #添加据库服务器唯一标识符，主库的server-id值必须比从库的小 #重启mysql [root@localhost ~]# systemctl restart mariadb Starting MySQL. SUCCESS! MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql_bin.000001 | 328 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
//配置主从 [root@chouyu ~]# vim /etc/my.cnf ... [mysqld] server-id = 20 relay-log = mysql_relay_bin [root@chouyu ~]# systemctl restart mariadb //配置从数据库 MariaDB [(none)]> change master to -> master_host='192.168.220.10', -> master_user='xixi', -> master_password='xixi123', -> master_log_file='mysql_bin.000001', -> master_log_pos=328; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> [root@chouyu ~]# systemctl restart mariadb MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.220.10 Master_User: xixi Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_bin.000002 Read_Master_Log_Pos: 328 Relay_Log_File: mysql_relay_bin.000006 Relay_Log_Pos: 627 Relay_Master_Log_File: mysql_bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
重启。再查看服务器状态
//往主库写入 MariaDB [(none)]> create database xixi; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> use xixi; Database changed MariaDB [xixi]> create table test(id int not null auto_increment,name varchar(20),age tinyint,primary key(id)); Query OK, 0 rows affected (0.003 sec) MariaDB [xixi]> insert test(name,age) values('z',34),('x',24),('c',25); Query OK, 3 rows affected (0.001 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [xixi]> select * from test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ 3 rows in set (0.000 sec) MariaDB [xixi]> //去从库看一下数据同步没有 MariaDB [(none)]> select * from xixi.test; +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ 3 rows in set (0.000 sec) MariaDB [(none)]>
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
主数据库：IP192.168.220.10 有数据
从数据库：IP192.168.220.20 无数据
//查看主库数据 [root@chouyu ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | xixi | +--------------------+ [root@chouyu ~]# mysql -uroot -p -e 'select * from xixi.test;' Enter password: +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ [root@chouyu ~]#
//从库上没数据 [root@chouyu ~]# mysql -uroot -p -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ [root@chouyu ~]#
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致 MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> [root@chouyu ~]# mysqldump -uroot -p --all-databases > all_$(date +%F_%T).sql Enter password: [root@chouyu ~]# [root@chouyu ~]# ls all_2021-01-03_08:54:50.sql all_2021-01-03_08:55:27.sql anaconda-ks.cfg [root@chouyu ~]#
//将备份数据推送至从服务器
[root@chouyu ~]# scp /root/all_2021-01-03_08\:55\:27.sql root@192.168.220.20:/root/
root@192.168.220.20's password:
all_2021-01-03_08:55:27.sql 100% 468KB 13.3MB/s 00:00
[root@chouyu ~]# mysql -uroot -p <all_2021-01-03_08\:55\:27.sql Enter password: [root@chouyu ~]# mysql -uroot -p -e 'show databases;' [root@chouyu ~]# mysql -uroot -p123123123 -e 'show databases;' Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | xixi | +--------------------+ [root@chouyu ~]# [root@chouyu ~]# mysql -uroot -p123123123 -e 'select * from xixi.test;' Enter password: +----+------+------+ | id | name | age | +----+------+------+ | 1 | z | 34 | | 2 | x | 24 | | 3 | c | 25 | +----+------+------+ [root@chouyu ~]#
