Mysql主从复制
摘要
由于单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力
Mysql的安装过程，建议参考我的这一篇文章：https://blog.csdn.net/weixin_43039456/article/details/81953693
实施规划
| 服务器 | IP | server-id |
|---|---|---|
| master | 192.168.142.142 | 11 |
| slave-1 | 192.168.142.143 | 22 |
| slave-2 | 192.168.142.144 | 33 |
同步时间
- 主服务器上安装配置NTP时间同步服务器
- 从服务器上进行时间同步
##主服务器操作
[root@localhost ~]# yum -y install ntpd
[root@localhost ~]# vim /etc/ntp.conf
restrict 192.168.142.0 mask 255.255.255.0 nomodify notrap \ #手动增加这一行，意思是指定192.168.142.0网段的主机都可以使用ntp服务器来同步时间
[root@localhost ~]# /etc/init.d/ntpd start
正在启动 ntpd： [确定]
##从服务器操作
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# /usr/sbin/ntpdate 192.168.142.142 \ #与主服务器进行时间同步
[root@localhost ~]# echo "*/30 * * * * /usr/sbin/ntpdate 192.168.142.142 &>/dev/null" >> /var/spool/cron/root \ #设置每半小时与主服务器进行时间同步
[root@localhost ~]# date \ #使用date命令查看服务器时间是否一致
主服务器操作（master）
- 启用二进制日志
- 配置唯一的server_id
- 创建有复制权限的用户账号
[root@localhost ~]# vim /etc/my.cnf \ #将以下配置添加至msyqld模块中
[mysqld]
server_id = 11
log-bin = mysql-bin
log-slave-updates = true
[root@localhost ~]# /etc/init.d/mysqld restart
[root@localhost ~]# mysql -u root -p
mysql> grant replication slave on *.* to 'adm'@'192.168.142.%' identified by '123123';
Query OK, 0 rows affected (0.05 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 408 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从服务器
- 启动中继日志。
- 配置唯一的server_id
- 使用有复制权限的用户账号连接至主节点，并启动复制线程。
salve-1
[root@localhost ~]# vim /etc/my.cnf \ #将以下配置添加至msyqld模块中
[mysqld]
server-id = 22
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@localhost ~]# /etc/init.d/mysqld restart
[root@localhost ~]# mysql -u root -p
mysql> change master to master_host='192.168.142.142',master_user='adm',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=408;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.38 sec)
mysql> show slave status \G; \ #确保Slave_IO_Running和Slave_SQL_Running两个值为Yes
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.142.142
Master_User: adm
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 408
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 408
Relay_Log_Space: 454
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: e7d78d6e-a630-11e8-993a-000c29b386d3
Master_Info_File: /public/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0参数说明
master_host：主服务器IP
master_user：主服务器用户名
master_password：主服务器密码
master_log_file：主服务器日志文件（此文件在主服务器上使用show master status命令查看）
master_log_pos：主服务器pos的位置（此处不能用引号）salve-2
[root@localhost ~]# vim /etc/my.cnf \ #将以下配置添加至msyqld模块中
[mysqld]
server-id = 33
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@localhost ~]# /etc/init.d/mysqld restart
[root@localhost ~]# mysql -u root -p
mysql> change master to master_host='192.168.142.142',master_user='adm',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=408;
Query OK, 0 rows affected, 2 warnings (0.08 sec)
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.142.142
Master_User: adm
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 408
Relay_Log_File: relay-log-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 408
Relay_Log_Space: 120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /public/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
测试
- 在主、从服务器上登录mysql
- 在主服务器上新建数据库db_test
- 在主、从服务器上分别查看数据库，显示数据库相同，则主从复制成功
##主服务器创建数据库
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.06 sec)
mysql>
## slave-1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql>
## slava-2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql>
1939
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
