异想天开

What's the true meaning of light, Could you tell me why

mysql 主从配置记录

日期:2019-05-27 19:28:52
  
最后更新日期:2019-05-27 19:39:34
mysql 主从配置原理:
主机每次操作都会记录到mysql-bin.xxx,从机的io线程使用专用账号登录主机,读取主机mysql-bin.xxx操作记录,转存到本地的relay_log,接着从机的sql线程重放该记录。

来一个配置实例, 主机为10.0.0.1, 从机为10.0.0.2。
环境: mysql Ver 15.1 Distrib 5.5.50-MariaDB

步骤1. 主机开启binlog。在/etc/my.conf的mysqld项添加
[code lang="cpp"]
server-id = 1
log-bin = mysql-bin
[/code]
systemctl restart mariadb重启mysql, 若datadir目录,产生了预期的mysql-bin.xxx,则正常。

步骤2. 配置从机
[code lang="cpp"]
server-id = 2 #设置server编号
log-bin = mysql-bin #开启binlog
replicate_wild_do_table=test.% #只同步test库下面的表
relay_log=mysqld-relay-bin #记录中继日志
log-slave-updates=YES #从服务器同步后记录日志
[/code]

步骤3. 主机添加从机访问账号,并添加复制权限
[code lang="cpp"]
grant replication slave on *.* to backup@10.0.0.2 identified by "yellow";
flush privileges
[/code]

步骤4. 登录主机,查看binlog偏移
[code lang="cpp"]
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 20 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
[/code]

步骤5. 登录从机
[code lang="cpp"]
change master to
master_host='10.0.0.1',
master_user='backup',
master_password='yellow',
master_log_file='mysql-bin.000001',
master_log_pos=20;
[/code]

测试,主机上面test库下面的表的操作,可以正常拷贝到从机。

可能遇到问题或增加的操作:
1. 主库加锁,拷贝表到从库,主从库开始处于一致的状态
use database_name
flush tables with read lock;
mysqldump -uroot -p test > test.sql
unlock tables;

2.
Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000001' at 800, the last event read from 'mysql-bin.000001' at 4, the last byte read from 'mysql-bin.000001' at 4.

binlog偏移不对,主机查看binlog偏移,停掉salve,重新同步
stop slave;
change master to
master_host='10.0.0.1',
master_user='backup',
master_password='yellow',
master_log_file='mysql-bin.000001',
master_log_pos=25;
start slave;

3.Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open'
主机需要开启binlog

4. Master command COM_REGISTER_SLAVE failed: Access denied for user 'backup'@'10.0.0.2' (using password: YES) (Errno: 1045)
专用账号添加replication slave权限