MySQL---主从复制
1.1 mysql主从复制:环境规划:集群中原有的db01服务器作为数据库主服务器,由于笔记本空间问题这里把backup备份服务器作为从服务器,不在安装新的机器,节省点空间,在backup服务器上部署好mysql即可
说明:实现主从复制的前提是两台服务器的时间同步要一致,前面ntp时间服务器已经搭建好,这里则不存在此问题
1.1.1 主服务器配置:
1. 修改配置文件
# tail /etc/my.cnf
server_id = 1
log-bin=master-bin
log-slave-updates=true
2. 重启数据库
# /etc/init.d/mysqld restart
3. 进入主数据库进行授权
mysql> grant replication slave on *.* to 'myslave'@'172.16.1.0/255.255.255.0'> Query OK, 0 rows affected (0.00 sec)
4. 显示position信息,一遍在从服务器端进行更改
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.1.2 从服务器配置:
1. 修改配置文件
# tail /etc/my.cnf
server_id = 2
relay-log=relay-log-bin
relay-log-index=slave=relay-bin.index
2. 启动mysql服务
3. 按照主服务器配置master-log-file 和 master—log-pos参数
mysql> change master to master_host='172.16.1.51',master_user='myslave',master_password='oldboy123',master_log_file='master-bin.000001',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.51
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 120
>
>
> Slave_IO_Running: Yes
Slave_SQL_Running: Yes 必须全为yes证明主从复制配置成功
1.1.3 遇到报错: 查看slave状态时显示Slave_IO_Running: No
在查看从库端查看slave状态时显示IO为no
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.1.51
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1645
>
>
> Slave_IO_Running: No
Slave_SQL_Running: Yes
这主要是以为在主库端的file和pos值 和从库端设置不同所导致,解决方案如下:
master端:
1. 重新启动数据库服务
# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.... SUCCESS!
2. 查看file和pos值
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)
slave端:
1. 进入数据库,停止slave
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
2. 重新写入file和pos值:
mysql> change master to master_log_file='master-bin.000002',master_log_pos=120;
Query OK, 0 rows affected (0.04 sec)
3. 启动slave:
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
4. 查看slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.51
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 120
>
>
> Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1.1.4 进行测试:
1. 在master端创建数据库
mysql> create database jiang;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dedecms |
| discuz |
| jiang |
| mysql |
| performance_schema |
| test |
| test2 |
| test3 |
| wordpress |
+--------------------+
10 rows in set (0.01 sec)
2. 在slave端查看数据库:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jiang | mysql主从复制配置成功!
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
页:
[1]