最简单的mysql主从复制

一、准备：

1、两台物理服务器，ip地址如下:
①、192.168.1.1
②、192.168.1.2

两台服务器上都必须关闭SELinux

• 关闭selinux:
  1、永久有效：修改/etc/sysconfig/selinux
  将文本中的SELINUX=enforcing，改为SELINUX=disabled。然后重启
  2、即时有效：setenforce 0
  3、查看状态：getenforce 如果为Permissive或者Disabled则已关闭

2、在两台服务器上都装上mysql服务器，我这里用的是Percona Server
安装方法可以见我的博客
两个mysql服务端口都是39847，并且已开放端口
用户名、密码都是：root、123456

二、Mysql主服务器配置

192.168.1.1这台服务器为主服务器

• 第一步：修改my.conf文件：
  在[mysqld]段下添加：

# 进行复制的库(可以不用)
binlog-do-db=db1
# 忽略复制的库(可以不用)
binlog-ignore-db=mysql
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=134

全部配置如下：

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name master generated for qinhelili@gmail.com at 2018-11-28 09:40:47

[mysql]

# CLIENT #
port                           = 39847
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
server_id                      = 10975
port                           = 39847

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
#启用二进制日志
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1
# 进行复制的库(可以不用)
# binlog-do-db				   = db1
# 忽略复制的库(可以不用)
binlog-ignore-db               = information_schema
binlog-ignore-db               = mysql
binlog-ignore-db               = performance_schema
binlog-ignore-db               = sys

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 8G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

• 第二步：重启mysql服务

service mysqld restart

• 第三步：建立帐户并授权给slave在192.168.1.2上可以连接

# 授权
mysql>GRANT SELECT, PROCESS, SUPER ,FILE,REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.1.2' IDENTIFIED BY '123456';

# 刷新权限
mysql> FLUSH PRIVILEGES;

一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。

• 第四步：查询master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      899 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

三、Mysql从服务器配置

• 第一步：修改my.conf文件：
  在[mysqld]段下添加：

#服务器唯一ID，一般取IP最后一段
server-id=434

全部配置如下：

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name slave generated for qinhelili@gmail.com at 2018-11-28 09:16:21

[mysql]

# CLIENT #
port                           = 39847
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
server_id                      = 11462
port                           = 39847

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# REPLICATION #
relay-log                      = /var/lib/mysql/relay-bin
slave-net-timeout              = 60

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 8G

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

• 第二步：重启mysql服务

service mysqld restart

• 第三步：建立连接

mysql>change master to master_host='192.168.1.1',master_port=39847,master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=899;
mysql>start slave;

注意语句中间不要断开，master_port为mysql服务器端口号(无引号)，master_user为执行同步操作的数据库账户，“120”无单引号(此处的899就是show master status 中看到的position的值，这里的mysql-bin.000001就是file对应的值)。

• 第四步：检查从服务器复制功能状态

mysql> show slave status\G;
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

错误处理：
如果出现此错误：
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be
different for replication to work.
因为是mysql是克隆的系统所以mysql的uuid是一样的，所以需要修改。
解决方法：
删除/var/lib/mysql/auto.cnf文件，重新启动服务。