WEB运维手册

Mysql主从

当数据库出现瓶颈，常用的方法就是 主从配置，也就是常说的读写分离方法。主写，从读，进行数据同步。

复制的三个步骤：

• master将改变记录到二进制日志(binary log)中
• slave将master的binary log events拷贝到它的中继日志(relay log)；
• slave重做中继日志中的事件，将改变反映它自己的数据。

#

测试环境 Centos 7 与 mariadb

将两台Mysql服务器分别安装

[root@Master ~]# yum -y install mariadb-server
[root@Master ~]# systemctl start mariadb
[root@Master ~]# mysqladmin -u root password '123456'

配置密钥登陆

[root@Master ~]# ssh-keygen
[root@Master ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]

配置hosts文件

192.168.1.104 Master
192.168.1.105 Slave

主数据库配置

[root@Master ~]# vi /etc/my.cnf

[mysqld]
server_id = 1
log-bin=binlog
log-bin-index=binlog.index
old_passwords=1

从数据库配置

[root@Slave ~]# vi /etc/my.cnf

server_id = 2
relay_log_purge=0
relay_log = /var/lib/mysql/mysql-relay-bin
relay_log_index = /var/lib/mysql/mysql-relay-bin.index
log-bin=binlog
log-bin-index=binlog.index
old_passwords=1

测试，配置完成后全部重启。

Master操作

mysql> GRANT replication slave ON *.* TO 'letong'@'%' identified by '123';
mysql> flush privileges;
mysql> show master status; #记住file和position

Slave操作

mysql> change master to master_host='192.168.1.104',master_port=3306,master_user='letong',master_password='123',master_log_file='binlog.000001',master_log_pos='245'

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.104
Master_User: kyo
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 245
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 526
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes
#以上两个Yes即成功

最后给予数据库互访的权限即可。