Mysql 一主一从

1. 主从原理

1.1 主从介绍

所谓 mysql 主从就是建立两个完全一样的数据库，其中一个为主要使用的数据库，另一个为次要的数据库，一般在企业中，存放比较重要的数据的数据库服务器需要配置主从，这样可以防止因数据库服务器宕机导致数据丢失，还能保证业务量太多、数据太多和访问人数太多时服务的质量（服务器响应速度），还能提供故障切换、读写分离、和备份等等功能
1.2 主从作用

• 实时灾备，用于故障切换
  
• 读写分离，提供查询服务
  
• 备份，避免影响业务
  

1.3 主从形式


一主一从
主主复制：当作备份使用，当主服务器出现故障时，另一个主服务器会自动顶上。
一主多从：用来实现读写分离，当写操作较少时，读操作较多时使用，主服务器用来实现写操作，从服务器用来实现读操作。
多主一从：用来实现读写分离，当写操作较多时，读操作较少时使用，主服务器用来实现写操作，从服务器用来实现读操作。5.7 开始支持
联级复制：是指从主场地复制过来的又从该场地再次复制到其他场地，即 A 场地把数据复制到 B 场地，B 场地又把这些数据或其中部分数据再复制到其他场地。
1.4 主从复制原理


主从复制步骤：

• 主库将所有的写操作记录到 binlog 日志中并生成一个 log dump 线程，将 binlog 日志传给从库的 I/O 线程
  
• 从库生成两个线程，一个 I/O 线程，一个 SQL 线程
  
  
  
  • I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log（中继日志） 文件中
    
  • SQL 线程，会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
    
  
  

2. 主从复制配置

主从复制配置步骤：

• 确保从数据库与主数据库里的数据一样
  
• 在主数据库里创建一个同步账号授权给从数据库使用
  
• 配置主数据库（修改配置文件）
  
• 配置从数据库（修改配置文件）
  

需求：
搭建两台 MySQL 服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
环境说明：

1. 数据库角色        IP        应用与系统版本        有无数据
2. 主数据库        192.168.23.4        Centos7.8 mysql-5.7        有数据
3. 从数据库        192.168.23.5        Centos7.8 mysql-5.7        无数据

复制代码

2.1Mysql安装

注意：需把安装包分别上传到两台服务器上分别执行下列命令进行安装

1. [root@localhost soft]# yum remove mysql-libs
2. [root@localhost soft]# rpm -qa | grep mariadb

复制代码

进行安装
将下载好的mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar上传

1. 安装相应软件
2. [root@localhost soft]# yum install -y openssl-devel.x86_64 openssl.x86_64 yum install -y libaio.x86_64 libaio-devel.x86_64 yum install -y perl.x86_64 perl-devel.x86_64 yum install -y perl-JSON.noarch yum install -y autoconf yum install -y wget yum install -y net-tools
4. 关闭防火墙
5. [root@localhost soft]# systemctl stop firewalld
6. [root@localhost soft]# systemctl disable firewalld
9. 解压软件包：
10. [root@localhost soft]# tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar

复制代码

1. [root@localhost soft]# rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
2. [root@localhost soft]# rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
3. [root@localhost soft]# rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
4. [root@localhost soft]# rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm
5. [root@localhost soft]# rpm -ivh mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm
6. [root@localhostsoft]#rpm -ivh mysql-community-embedded-compat-5.7.22-1.el7.x86_64.rpm
7. [root@localhost soft]# rpm -ivh mysql-community-devel-5.7.22-1.el7.x86_64.rpm
8. [root@localhost soft]# rpm -ivh mysql-community-test-5.7.22-1.el7.x86_64.rpm
9. 查看安装版本：
10. [root@localhost soft]# mysql -V

复制代码

编辑配置文件
vim /etc/my.cnf
#跳过登录验证  无需密码即可登录mysql
skip-grant-tables
# 设置默认字符集UTF-8
character_set_server=utf8
collation-server=utf8_general_ci
# 设置默认字符集UTF-8
init_connect='SET NAMES utf8'
# 设置数据库日志过期天数为14天
server_id=1
expire_logs_days=14
[client]
default-character-set=utf8

1. ##添加
2. skip-grant-tables
3. character_set_server=utf8
4. collation-server=utf8_general_ci
5. init_connect='SET NAMES utf8'
6. server_id=1
7. expire_logs_days=14
8. [client]
9. default-character-set=utf8

复制代码

1. 启动mysql
2. [root@localhost soft]# systemctl start mysqld.service
3. [root@localhost soft]# systemctl status mysqld

复制代码

1. 获取mysql的root用户的初始密码
2. [root@localhost soft]# grep 'temporary password' /var/log/mysqld.log

复制代码

1. 以获取mysql的root用户的初始密码登录数据库
2. [root@localhost soft]# mysql -u root -pAcqWlI64o:kG

复制代码

1. 修改root密码
2. mysql> flush privileges;
3. mysql> set password for root@localhost=password('123456');
4. 使密码即时生效
5. mysql> flush privileges;
6. 允许以root身份远程登录mysql
7. mysql> grant all privileges on *.* to root@'%' identified by "123456";
8. mysql> flush privileges;

复制代码

2.2 mysql 主从配置
2.2.1 确保从数据库于主数据库的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

1. //主库为master
2. [root@localhost ~]# hostnamectl set-hostname master
3. [root@localhost ~]# bash
4. [root@master ~]#

复制代码

1. //从库为slave
2. [root@localhost ~]# hostnamectl set-hostname slave
3. [root@localhost ~]# bash
4. [root@slave ~]#

复制代码

1. //先查看主库有哪些库
2. [root@master soft]# mysql -uroot -p123456 -e‘show databases;'
3. mysql: [Warning] Using a password on the command line interface can be insecure.
4. +--------------------+
5. | Database           |
6. +--------------------+
7. | information_schema |
8. | mysql              |
9. | performance_schema |
10. | sys                |
11. | test               |
12. +--------------------+
13. //在查看从库
14. [root@slave soft]# mysql -uroot -p123456 -e‘show databases;'
15. mysql: [Warning] Using a password on the command line interface can be insecure.
16. +--------------------+
17. | Database           |
18. +--------------------+
19. | information_schema |
20. | mysql              |
21. | performance_schema |
22. | sys                |
23. +--------------------+

复制代码

//全备主库 //全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致

1. mysql> flush tables with read lock;
2. Query OK, 0 rows affected (0.00 sec)
3. //此锁表的终端必须在备份完成以后才能退出

复制代码

1. //备份主库并将备份文件传送到从库
2. [root@master soft]# mysqldump -uroot -p123456 --all-databases > /opt/acc-2022.sql;
3. mysqldump: [Warning] Using a password on the command line interface can be insecure.
4. [root@master ~]# ls /opt/

复制代码

 

1. [root@master soft]# scp /opt/acc-2022.sql root@192.168.23.5:/opt/

复制代码

1. //解除主库的锁表状态，直接退出交互式界面即可
2. mysql> quit
3. Bye
4. [root@master ~]#
6. //在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
7. [root@slave ~]# mysql -uroot -p123456 < /opt/acc-2022.sql
8. mysql: [Warning] Using a password on the command line interface can be insecure.
9. [root@slave ~]# mysql -uroot -p123456 -e'show databases';
10. mysql: [Warning] Using a password on the command line interface can be insecure.
11. +--------------------+
12. | Database           |
13. +--------------------+
14. | information_schema |
15. | mysql              |
16. | performance_schema |
17. | sys                |
18. | test               |
19. +--------------------+
20. [root@slave ~]#

复制代码

2.2.3 配置主数据库

1. [root@master soft]# vim /etc/my.cnf
2. //在[mysql]这段后面添加
3. datadir=/var/lib/mysql
4. socket=/var/lib/mysql/mysql.sock
5. log-bin=mysql-bin //添加 启用binlog日志
6. server-id=1  //添加 数据库服务器唯一标识符，主库的server-id值必须比从库的小
7. # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
8. log-error=/var/log/mysqld.log
9. pid-file=/var/run/mysqld/mysqld.pid

复制代码

1. //重启数主库的mysql服务
2. [root@master soft]# systemctl restart mysqld
3. [root@master soft]# ss -anlt
4. State      Recv-Q Send-Q                              Local Address:Port                                             Peer Address:Port
5. LISTEN     0      128                                             *:22                                                          *:*
6. LISTEN     0      128                                          [::]:22                                                       [::]:*
7. LISTEN     0      80                                           [::]:3306                                                     [::]:*
8. [root@master soft]#
11. //查看主库状态
12. [root@master soft]# mysql -uroot -p123456
13. mysql: [Warning] Using a password on the command line interface can be insecure.
14. Welcome to the MySQL monitor.  Commands end with ; or \g.
15. Your MySQL connection id is 2
16. Server version: 5.7.22-log MySQL Community Server (GPL)
18. Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
20. Oracle is a registered trademark of Oracle Corporation and/or its
21. affiliates. Other names may be trademarks of their respective
22. owners.
24. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
26. mysql> show master status;
27. +------------------+----------+--------------+------------------+-------------------+
28. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
29. +------------------+----------+--------------+------------------+-------------------+
30. | mysql-bin.000001 |      154 |              |                  |                   |
31. +------------------+----------+--------------+------------------+-------------------+
32. 1 row in set (0.00 sec)
34. mysql>

复制代码

2.2.4 配置从数据库

1. [root@slave ~]# vim /etc/my.cnf
2. //在[mysql]这段后面添加
3. datadir=/var/lib/mysql
4. socket=/var/lib/mysql/mysql.sock
5. server-id=2  //添加 从库的server-id比主库的大
6. relay-log=mysql-relay-bin  //添加
7. # Disabling symbolic-links is recommended to prevent assorted security risks
8. symbolic-links=0
9. log-error=/var/log/mysqld.log
10. pid-file=/var/run/mysqld/mysqld.pid

复制代码

1. //重启mysql服务
2. [root@slave ~]# systemctl restart mysqld
3. [root@slave ~]# ss -anlt
4. State      Recv-Q Send-Q                              Local Address:Port                                             Peer Address:Port
5. LISTEN     0      128                                             *:22                                                          *:*
6. LISTEN     0      128                                          [::]:22                                                       [::]:*
7. LISTEN     0      80                                           [::]:3306                                                     [::]:*
8. [root@slave ~]#
11. //配置并启动主从复制
12. [root@slave ~]# mysql -u root -p123456
13. mysql: [Warning] Using a password on the command line interface can be insecure.
14. Welcome to the MySQL monitor.  Commands end with ; or \g.
15. Your MySQL connection id is 2
16. Server version: 5.7.22 MySQL Community Server (GPL)
18. Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
20. Oracle is a registered trademark of Oracle Corporation and/or its
21. affiliates. Other names may be trademarks of their respective
22. owners.
24. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
25. mysql> change master to
26.     -> master_host='192.168.23.4',
27.     -> master_user='hqd',
28.     -> master_password='123456',
29.     -> master_log_file='mysql-bin.000001',
30.     -> master_log_pos=154;
31. Query OK, 0 rows affected, 2 warnings (0.03 sec)
33. mysql> start slave;
34. Query OK, 0 rows affected (0.00 sec)
36. mysql>
37. //查看服务器状态
38. mysql> show slave status \G
39. *************************** 1. row ***************************
40.                Slave_IO_State: Waiting for master to send event
41.                   Master_Host: 192.168.23.4
42.                   Master_User: hqd
43.                   Master_Port: 3306
44.                 Connect_Retry: 60
45.               Master_Log_File: mysql-bin.000002
46.           Read_Master_Log_Pos: 154
47.                Relay_Log_File: mysql-relay-bin.000004
48.                 Relay_Log_Pos: 367
49.         Relay_Master_Log_File: mysql-bin.000002
50.              Slave_IO_Running: Yes
51.             Slave_SQL_Running: Yes
52.               Replicate_Do_DB:
53.           Replicate_Ignore_DB:

复制代码

3 测试验证

1. 在主服务器创建‘hqd’名称的库 查看从服务器是否同步
2. mysql> create database hqd;
3. Query OK, 1 row affected (0.00 sec)
5. mysql> show databases;
6. +--------------------+
7. | Database           |
8. +--------------------+
9. | information_schema |
10. | hqd                |
11. | mysql              |
12. | performance_schema |
13. | sys                |
14. | test               |
15. +--------------------+
16. 6 rows in set (0.00 sec)
17. mysql>

复制代码

1. 在从数据库中查看数据是否同步
2. [root@slave ~]# mysql -uroot -p123456
3. mysql: [Warning] Using a password on the command line interface can be insecure.
4. Welcome to the MySQL monitor.  Commands end with ; or \g.
5. Your MySQL connection id is 5
6. Server version: 5.7.22 MySQL Community Server (GPL)
8. Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
10. Oracle is a registered trademark of Oracle Corporation and/or its
11. affiliates. Other names may be trademarks of their respective
12. owners.
14. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16. mysql> show databases;
17. +--------------------+
18. | Database           |
19. +--------------------+
20. | information_schema |
21. | hqd                |
22. | mysql              |
23. | performance_schema |
24. | sys                |
25. | test               |
26. +--------------------+
27. 6 rows in set (0.00 sec)
29. mysql>

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！