2.Mysql主从复制基本配置与实现

发表于

MySQL主从复制原理图

MySql主从复制原理图

MySQL主库

更改主库配置/etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id=1 #主库从库不能一样
log-bin=/var/lib/mysql/myslq-bin #二进制日志目录

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

重启MySQL让配置文件生效

1
systemctl restart mariadb

登录主库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql>show variables like 'log_in';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
mysql>show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
###
###主库添加授权用户
mysql>grant replication slave on *.* to 'rep'@'192.168.1.%' identified by '123456';
###刷新数据库
mysql>flush privileges;

备份数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
###主库锁表：
mysql>flush tables with read lock;
###查看主库位置点
mysql>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| myslq-bin.000001 |      937 |              |                  |
+------------------+----------+--------------+------------------+
###使用mysqddump备份
mysqldump -uroot -p123456 -A -B --events --master-data=2 > bak.sql
###该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，
   并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句
   形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器
   二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写SQL注释。
   如果value被省略，这是默认动作。
###主库解锁：
mysql>unlock tables;

备份文件bak.sql拷贝到从库服务器上

MySql从库

从库配置/etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
server-id=2
log_bin=OFF
relay_log=/var/lib/mysql/relay_log

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

导入备份文件

1
mysql -uroot -p123456 < bak.sql

登录从库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql>change master to
    ->master_host='192.168.1.1',
    ->master_port='3306',
    ->master_user='rep',
    ->master_password='123456',
    ->master_log_file='mysql-bin.000001',
    ->master_log_pos='937';
###从库开启slave
mysql>start slave;
###检查从库状态
mysql>show slave status\G;

###以下两项都为yes,证明主从配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

注：如果在从库上执行了写入操作，会导致Slave_SQL_Running线程down掉