MySQL主从复制

• 1. 部署安装MySQL
• 2. 配置主从同步
• • 2.1 授权一个数据库用户用来测试
  • 2.2 查看主库的状态
  • 2.3 修改从数据库的配置文件
  • 2.4 进入数据库配置主从并启动主从复制
  • 2.5 在主数据库创建一个库进行测试看能否同步成功。
  • 2.6 主数据库有数据的情况下实现主从复制
  • 2.7 在主库上先进行全备，再将全备文件传到从库上去
  • • 2.7.1 在主数据库上授权一个用户给从数据库使用
    • 2.7.2 将主库上面的File和Position按照下面的方式写入
    • 2.7.3 在主库上插入一条数据进行测试,看从库是否同步过来了。

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)