mysql主从

1. 主从简介

在现代企业中,数据显得尤为重要,而存储数据的数据库选择又五花八门,但无论是何种数据库,均存在着一种隐患。

1.1 主从作用

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

1.2 主从形式

在这里插入图片描述

2. 主从复制原理

在这里插入图片描述

3. 主从复制配置

主从复制配置步骤:

1.确保从数据库与主数据库里的数据一样
2.在主数据库里创建一个同步账号授权给从数据库使用
3.配置主数据库(修改配置文件)
4.配置从数据库(修改配置文件)

需求:

搭建两台MySQL服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作

环境说明:

数据库角色IP应用与系统版本有无数据
主数据库192.168.234.11centos7/redhat7
mysql-5.7
有数据
从数据库192.168.234.22centos7/redhat7
mysql-5.7
无数据

3.1 mysql安装

分别在主从两台服务器上安装mysql-5.7版本,此处略过安装步骤,若有疑问请参考《mysql基础》与《mysql进阶》两篇文章。

3.2 mysql主从配置一

3.2.1 确保从数据库与主数据库里的数据一样

为确保从数据库与主数据库里的数据一样,先全备主数据库并还原到从数据库中

//先查看主库有哪些库
[root@master ~]# mysql mysql -uroot -p'wyz123!' -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wyz                |
+--------------------+
//再查看从库有哪些库
[root@salve1 ~]# mysql mysql -uroot -p'wyz123!' -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

//全备主库
//全备主库时需要另开一个终端,给数据库加上读锁,避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

[root@master ~]# mysqldump mysql -uroot -p'wyz123!' --all-databases > /opt/all-201905151555.sql
[root@master ~]# ls /opt/
all-201905151555.sql  data
[root@master ~]# scp /opt/all-201905151555.sql  root@192.168.234.22:/opt
The authenticity of host '192.168.234.22 (192.168.234.22)' can't be established.
ECDSA key fingerprint is SHA256:EkkMNw27hWFw+L8KTq0RzTH0QNIwHrdoaY3aioImEJM.
ECDSA key fingerprint is MD5:b5:50:44:19:f1:23:36:7f:55:42:ba:f1:23:c1:ce:eb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.234.22' (ECDSA) to the list of known hosts.
root@192.168.234.22's password: 
all-201905151555.sql 

//解除主库的锁表状态,直接退出交互式界面即可
mysql> quit
Bye

//在从库上恢复主库的备份并查看从库有哪些库,确保与主库一致
[root@salve1 ~]# ls /opt/
all-201905151555.sql  data  rh
[root@salve1 ~]# mysql mysql -uroot -p'wyz123!' < /opt/all-201905151555.sql 
[root@salve1 ~]# mysql mysql -uroot -p'wyz123!' -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wyz                |
+--------------------+

3.2.2 在主数据库里创建一个同步账号授权给从数据库使用
mysql> grant all on *.* to 'repl'@'192.168.234.22' identified by 'repl123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.2.3 配置主数据库
[root@master ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

log-bin=mysql-bin   //启用binlog日志
server-id=1			//数据库服务器唯一标识符,主库的server-id值必须比从库的大
symbolic-links=0

//重启mysql服务
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@master ~]# ss -antl
State       Recv-Q Send-Q      Local Address:Port                     Peer Address:Port              
LISTEN      0      128                     *:22                                  *:*                  
LISTEN      0      100             127.0.0.1:25                                  *:*                  
LISTEN      0      128                    :::22                                 :::*                  
LISTEN      0      100                   ::1:25                                 :::*                  
LISTEN      0      80                     :::3306                               :::* 

//查看主库的状态
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)

3.2.4 配置从数据库
[root@salve1 ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=2
relay-log=mysql-relay-bin

symbolic-links=0

[root@salve1 ~]# !serv
service mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@salve1 ~]# ss -antl
State       Recv-Q Send-Q      Local Address:Port                     Peer Address:Port                               
LISTEN      0      5           192.168.122.1:53                                  *:*                  
LISTEN      0      128                     *:22                                  *:*                                                   
LISTEN      0      128                    :::22                                 :::*                                  
LISTEN      0      100                   ::1:25                                 :::*                                 
LISTEN      0      80                     :::3306                               :::*  

change master to master_host='192.168.234.11',master_user='repl',master_password='repl123!',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>  show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.234.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 

3.2.5 测试验证

在主服务器的wyz库的wangyizhen表中插入数据:

mysql> use wyz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from wangyizhen;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   23 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> insert into wangyizhen values (3,'sean',20),(4,'wyz',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from wangyizhen;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   23 |
|  3 | sean  |   20 |
|  4 | wyz   |   23 |
+----+-------+------+
4 rows in set (0.00 sec)

在从数据库中查看数据是否同步:

mysql> use wyz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from wangyizhen;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   20 |
|  2 | jerry |   23 |
|  3 | sean  |   20 |
|  4 | wyz   |   23 |
+----+-------+------+
4 rows in set (0.00 sec)

主从配置二(均无数据)

主从复制配置步骤:

1.在主数据库里创建一个同步账号授权给从数据库使用
2.配置主数据库(修改配置文件)
3.配置从数据库(修改配置文件)

需求:

搭建两台MySQL服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作

环境说明:

数据库角色IP应用与系统版本有无数据
主数据库192.168.234.11centos7/redhat7
mysql-5.7
无数据
从数据库192.168.234.22centos7/redhat7
mysql-5.7
无数据

mysql安装

分别在主从两台服务器上安装mysql-5.7版本,此处略过安装步骤,若有疑问请参考《mysql基础》与《mysql进阶》两篇文章。

mysql主从配置
在主数据库里创建一个同步账号授权给从数据库使用

mysql> grant all on *.* to 'repl'@'192.168.234.22' identified by 'repl123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置主数据库

[root@master ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

log-bin=mysql-bin   
server-id=1	
symbolic-links=0

//重启mysql服务
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@master ~]# ss -antl
State       Recv-Q Send-Q      Local Address:Port                     Peer Address:Port              
LISTEN      0      128                     *:22                                  *:*                  
LISTEN      0      100             127.0.0.1:25                                  *:*                  
LISTEN      0      128                    :::22                                 :::*                  
LISTEN      0      100                   ::1:25                                 :::*                  
LISTEN      0      80                     :::3306                               :::* 

//查看主库的状态
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)

配置从数据库

[root@salve1 ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=2
relay-log=mysql-relay-bin

symbolic-links=0

[root@salve1 ~]# service mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@salve1 ~]# ss -antl
State       Recv-Q Send-Q      Local Address:Port                     Peer Address:Port                               
LISTEN      0      100             127.0.0.1:25                                  *:*                   
LISTEN      0      128                     *:22                                  *:*                                                   
LISTEN      0      128                    :::22                                 :::*                                  
LISTEN      0      100                   ::1:25                                 :::*                                 
LISTEN      0      80                     :::3306                               :::*  

change master to master_host='192.168.234.11',master_user='repl',master_password='repl123!',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql>  show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.234.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 

测试验证
在主服务器创建wyz数据库和wang表,并向wyz库的wang表中插入数据:

mysql> create database wyz;
mysql> use wyz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table wang (id not null,name varchar(100) not null,age tinyint);
mysql> insert into wang values (1,'sean',20),(2,'wyz',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | sean  |   20 |
|  2 | wyz   |   23 |
+----+-------+------+
2 rows in set (0.00 sec)

在从数据库中查看数据是否同步:

mysql> use wyz;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from wang;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | sean  |   20 |
|  2 | wyz   |   23 |
+----+-------+------+
2 rows in set (0.00 sec)
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值