MySQL主从复制
1. 部署安装MySQL
##首先下载MySQL的rpm包,并解压
[root@slave local]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
[root@slave local]# tar -xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
// 创建MySQL用户
[root@slave local]# useradd -M -r -s /sbin/nologin mysql
// 为方便操作创建软连接
[root@slave local]# ln -s mysql-5.7.32-linux-glibc2.12-x86_64/ mysql
// 改变属主和属组
[root@slave local]# chown -R mysql.mysql mysql
// 配置环境变量
[root@slave local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
重新加载文件
[root@slave local]# source /etc/profile.d/mysql.sh
// 更改属主和属组
[root@slave local]# chown -R mysql.mysql /opt/data/
// 初始化数据库
[root@slave local]# mysqld --initialize --user mysql --datadir /opt/data/
// 编写配置文件
[root@master opt]# vim /etc/my.cnf
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-resolve
[root@slave support-files]# pwd
/usr/local/mysql/support-files
[root@slave support-files]# cp mysql.server /etc/init.d/mysqld
找到下面basedir添加下面的内容
[root@slave support-files]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
// 启动数据库
[root@slave data]# service mysqld start
Starting MySQL.Logging to '/opt/data/slave.err'.
SUCCESS!
用初始化数据库生成的密码登录数据库
[root@slave data]# mysql -uroot -p'qnj3s5auF3)p'
// 进入数据库自己设置新密码
mysql> set password = password('新密码');
2. 配置主从同步
// 两个数据库都是一模一样的
[root@master ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@slave ~]# mysql -uroot -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
2.1 授权一个数据库用户用来测试
mysql> grant replication slave on *.* to 'test'@'192.168.182.139' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@slave ~]# mysql -utest -p123 -h192.168.182.138
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 8
Server version: 5.7.32 MySQL Community Server (GPL)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
在主数据库的配置文件里面添加server-id和bin-log日志在[msqyld]的最下面添加，添加之后重启MySQL
server-id = 10
log-bin = mysql_bin
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
2.2 查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.3 修改从数据库的配置文件
也是在[mysqld]的最下面添加下面两行，并重启MySQL
[root@slave ~]# vim /etc/my.cnf
server-id = 20
relay-log = mysql_relay
[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
2.4 进入数据库配置主从并启动主从复制
// 这里的 master_log_file和 master_log_pos要与主数据库的状态保持一致
mysql> change master to
-> master_host='192.168.182.138',
-> master_user='test',
-> master_password='123',
-> master_log_file='mysql_bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.11 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.182.138
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql_relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.5 在主数据库创建一个库进行测试看能否同步成功。
mysql> create database data;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2.6 主数据库有数据的情况下实现主从复制
先将配置文件里面的server-id和log-bin删除
在主库上创建一个库和表
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> create table student(id int not null auto_increment primary key,name varchar(100)not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)
mysql> insert student (name,age) values ('tom',15),('admin',17),('kill',18);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
// 全备数据库,在全备数据库之前先给数据库加上读锁避免在备份期间有人操作导致导入的数据不一致
mysql> flush tables with read lock;
下面这个命令可以查看你数据库的命令运行状态
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 2 | root | localhost | test | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
2.7 在主库上先进行全备，再将全备文件传到从库上去
[root@master ~]# mysqldump -uroot -p --all-databases > $(date '+%Y%m%d').sql
Enter password:
[root@master ~]# ls
20210830 anaconda-ks.cfg m.sh w.sh year.sh
20210830.sql httpd.conf passwd x.sh
[root@master ~]# scp 20210830.sql 192.168.182.139:/root
root@192.168.182.139's password:
20210830.sql
[root@slave ~]# ls
20210830.sql anaconda-ks.cfg m.sh passwd w.sh x.sh year.sh
[root@slave ~]# mysql -uroot -p < 20210830.sql
[root@slave ~]# mysql -uroot -p -e "show databases;"
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
此时主从数据的数据一样
2.7.1 在主数据库上授权一个用户给从数据库使用
mysql> grant replication slave on *.* to 'test'@'192.168.182.139' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
将server-id和log-bin重新写如配置文件，然后重启服务
[root@master ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
2.7.2 将主库上面的File和Position按照下面的方式写入
mysql> change master to
-> master_host='192.168.182.138',
-> master_user='test',
-> master_password='123',
-> master_log_file='mysql_bin.000003',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
开启主从同步
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: 192.168.182.138
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql_relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.7.3 在主库上插入一条数据进行测试,看从库是否同步过来了。
mysql> insert student (name,age) value('sq',19);
Query OK, 1 row affected (0.00 sec)
发现同步成功。
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 15 |
| 2 | admin | 17 |
| 3 | kill | 18 |
| 4 | sq | 19 |
+----+-------+------+
4 rows in set (0.00 sec)