文章目录
1. 主从简介
在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。
1.1 主从作用
实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务
1.2 主从形式
2. 主从复制原理
3. 主从复制配置
主从复制配置步骤：
1.确保从数据库与主数据库里的数据一样
2.在主数据库里创建一个同步账号授权给从数据库使用
3.配置主数据库（修改配置文件）
4.配置从数据库（修改配置文件）
需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
环境说明：
| 数据库角色 | IP | 应用与系统版本 | 有无数据 |
|---|---|---|---|
| 主数据库 | 192.168.234.11 | centos7/redhat7 mysql-5.7 | 有数据 |
| 从数据库 | 192.168.234.22 | centos7/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.11 | centos7/redhat7 mysql-5.7 | 无数据 |
| 从数据库 | 192.168.234.22 | centos7/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)
1249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
