MySQL 高可用

配置 Master-Slave

my.cnf 默认位置:

1
/etc/mysql/mysql.conf.d/mysqld.cnf

配置 Master:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

# 添加如下三行
# 二进制日志产生的所有文件的基本名
log-bin = master-bin
# 二进制索引文件的文件名
log-bin-index = master-bin.index
# 每个服务器都有一个唯一的服务器 ID
server-id = 1

重启 Master 使配置生效:

1
sudo service mysql restart

Slave 启动一个标准的客户端连到 Master,并请求 Master 将所有的改动转储给它。Slave 连接时要求 Master 上有一个特殊复制权限的用户:

1
2
mysql> CREATE USER repl_user;
mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY 'xyzzy';

进去 mysql 数据文件夹

1
2
3
4
5
sudo -i
cd /var/lib/mysql

# sudo -i, --login
# Run the shell specified by the target user's password database entry as a login shell.

开启 binlog

是否开启

1
2
3
4
5
6
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+

开启

一定要确保开启,对于恢复数据很方便:

1
2
3
4
# /etc/mysql/mysql.conf.d/mysqld.cnf
log-bin = /var/lib/mysql/mysql-binlog
# >= 5.7 必须指定, ID 不和集群(如果有)中的其它机器重名
server-id = 1

推荐文章