180123 MySQL主从介绍和配置

MySQL主从介绍

• MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做做主从后，在A上写数据，另一台B也会跟着写数据，两者数据实时同步
• MySQL主从是基于binlog的，主上需要开启binlog才能进行主从。
• 主从过程大致有3歌步骤 1主将更改操作记录到binlog里 2从将主的binlog事件（sql语句）同步到本机上并记录在relaylog里 3从根据relaylog里面的sql语句按顺序执行
• 主上有一个log dump线程，用来和从的I/O线程传递binlog
• 从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

主从配置-主上操作

• 安装mysql

• 修改my.cnf，增加server-id=130和log_bin=user1

• 修改完配置文件后，启动或重启mysqld服务

• 把mysql库备份并恢复成user库，作为测试数据

• mysqldump -uroot -e "create database user"

• mysql -uroot user < /tmp/mysql.sql

• 创建用作同步数据的用户

• grant replication slave on . to 'repl'@slave_ip identified by 'password';

• flush tables with read lock;

• show master status;

主从配置 - 主上操作

1. 在两台机器安装并启动mysql服务后，首先在主上进行操作
2. 修改/etc/mys.cnf配置文件

• 在配置文件下[mysqld]下添加
  • server-id=15 这个id可以自定义，这里根据ip来定义
  • log_bin=aminglinux1 打开binlog

[root@node15 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL... SUCCESS! 
[root@node15 ~]# ls -lt
总用量 4
drwxr-x---. 2 root root   19 1月  24 09:18 logs
-rw-------. 1 root root 1649 1月   2 22:17 anaconda-ks.cfg
[root@node15 ~]# cd /data/mysql/
[root@node15 mysql]# ls -lt
总用量 110672
-rw-rw----. 1 mysql mysql 50331648 1月  27 19:16 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 1月  27 19:16 ibdata1
-rw-rw----. 1 mysql mysql    45249 1月  27 19:16 node15.err
-rw-rw----. 1 mysql mysql        5 1月  27 19:16 node15.pid
-rw-rw----. 1 mysql mysql       21 1月  27 19:16 aminglinux1.index
-rw-rw----. 1 mysql mysql      120 1月  27 19:16 aminglinux1.000001
drwx------. 2 mysql mysql     4096 1月  24 09:30 zrlog
drwx------. 2 mysql mysql     4096 1月  16 09:59 mysql2
-rw-rw----. 1 mysql mysql       56 1月   3 08:56 auto.cnf
drwx------. 2 mysql mysql     4096 1月   3 08:54 mysql
drwx------. 2 mysql mysql     4096 1月   3 08:54 performance_schema
-rw-rw----. 1 mysql mysql 50331648 1月   3 08:54 ib_logfile1
drwx------. 2 mysql mysql        6 1月   3 08:45 test

做个备份

[root@node15 mysql]# mysqldump -uroot -paminglinux zrlog > /tmp/zrlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@node15 mysql]# du -sh /tmp/zrlog.sql
12K /tmp/zrlog.sql

创建一个新库

[root@node15 mysql]# mysql -uroot -paminglinux -e "create database aming"
Warning: Using a password on the command line interface can be insecure.

8. 创建好库后，还需要把数据恢复一下，那也就是说做的主从，参考的对象就是 aming这个库

[root@node15 mysql]# mysql -uroot -paminglinux aming < /tmp/zrlog.sql 
Warning: Using a password on the command line interface can be insecure.
[root@node15 mysql]# ls -lt
总用量 225372
-rw-rw----. 1 mysql mysql 50331648 1月  27 19:26 ib_logfile0
-rw-rw----. 1 mysql mysql 79691776 1月  27 19:26 ibdata1
-rw-rw----. 1 mysql mysql    10546 1月  27 19:26 aminglinux1.000001
drwx------. 2 mysql mysql     4096 1月  27 19:26 aming
-rw-rw----. 1 mysql mysql    45249 1月  27 19:16 node15.err
-rw-rw----. 1 mysql mysql        5 1月  27 19:16 node15.pid
-rw-rw----. 1 mysql mysql       21 1月  27 19:16 aminglinux1.index
drwx------. 2 mysql mysql     4096 1月  24 09:30 zrlog
drwx------. 2 mysql mysql     4096 1月  16 09:59 mysql2
-rw-rw----. 1 mysql mysql       56 1月   3 08:56 auto.cnf
drwx------. 2 mysql mysql     4096 1月   3 08:54 mysql
drwx------. 2 mysql mysql     4096 1月   3 08:54 performance_schema
-rw-rw----. 1 mysql mysql 50331648 1月   3 08:54 ib_logfile1
drwx------. 2 mysql mysql        6 1月   3 08:45 test

下面创建用于主从相互同步数据的用户

mysql> grant replication slave on *.* to 'repl'@'192.168.88.35' identified by 'aminglinux111';
Query OK, 0 rows affected (0.00 sec)
锁定表，目的是不让表继续写，

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

查看一下binlog的文件和大小，并记住binlog的filename

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

• 备份其他的库

[root@node15 mysql]# mysqldump -uroot -paminglinux mysql2 > /tmp/my2.sql
Warning: Using a password on the command line interface can be insecure.
[root@node15 mysql]# mysqldump -uroot -paminglinux test > /tmp/test.sql
Warning: Using a password on the command line interface can be insecure.
[root@node15 mysql]# ls /tmp/*sql
/tmp/my2.sql     /tmp/mysql_all.sql  /tmp/mysql.sql  /tmp/user.sql
/tmp/mysql2.sql  /tmp/mysqlbak.sql   /tmp/test.sql   /tmp/zrlog.sql

等会把/tmp/目录下 .sql文件都拷贝到 从上 去

从上操作

• 安装mysql
• 查看my.cnf，配置server-id=132，要求和主不一样
• 修改完配置文件后，启动或者重启mysqld服务
• 把主上aming库同步到从上
• 可以先创建aming库，然后把主上的/tmp/mysql.sql拷贝到从上，然后导入aming库
• mysql -uroot
• stop slave；
• change master to master_host='', master_user='repl', master_password='', master_log_file='', master_log_pos=xx,
• start slave;
• 还要到主上执行 unlock tables

---

主从配置 - 从上操作

• 在从上机器配置

1. 首先在从上安装并启动mysql，然后查看my.cnf，配置server-id=35，要求和主不一样，在配置文件的 log_bin参数 就不需要配置的，因为只有 主上 才需要二进制日志文件

[root@node35 ~]# vi /etc/my.cnf
[root@node35 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL................... SUCCESS! 

把主机器上备份的 .sql 数据，拷贝到从机器上，然后做一个数据恢复

[root@node35 ~]# scp 192.168.88.15:/tmp/*.sql /tmp/
The authenticity of host '192.168.88.15 (192.168.88.15)' can't be established.
ECDSA key fingerprint is 59:0b:64:05:e0:e2:9f:a0:16:79:7b:a6:87:b3:c9:da.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.88.15' (ECDSA) to the list of known hosts.
root@192.168.88.15's password: 
my2.sql                                                                       100%  642KB 641.9KB/s   00:00    
mysql2.sql                                                                    100%   30KB  30.1KB/s   00:00    
mysql_all.sql                                                                 100% 1283KB   1.3MB/s   00:00    
mysqlbak.sql                                                                  100%  641KB 641.5KB/s   00:00    
mysql.sql                                                                     100%    0     0.0KB/s   00:00    
test.sql                                                                      100% 1258     1.2KB/s   00:00    
user.sql                                                                      100% 6998     6.8KB/s   00:00    
zrlog.sql                                                                     100%   10KB   9.8KB/s   00:00    
[root@node35 ~]# 

mysql> create database aming;
Query OK, 1 row affected (0.00 sec)


mysql> create database zrlog;
Query OK, 1 row affected (0.01 sec)

mysql> create database mysql2;
Query OK, 1 row affected (0.04 sec)

mysql> quit
Bye

然后将数据库做一个恢复

[root@node35 ~]# mysql -uroot zrlog < /tmp/zrlog.sql
[root@node35 ~]# mysql -uroot mysql2 < /tmp/mysql2.sql

• 保证两边数据一致

[root@node35 ~]# ls /data/mysql/
aming     ibdata1      ib_logfile1  mysql2      node35.pid          test
auto.cnf  ib_logfile0  mysql        node35.err  performance_schema  zrlog

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> change master to master_host='192.168.88.15', master_user='repl', master_password='aminglinux111', master__log_file='aminglinux1.000001', master_log_pos=10961;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

通过 show slave status\G 判断主从是否配置成功
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.88.15
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: aminglinux1.000001
          Read_Master_Log_Pos: 10961
               Relay_Log_File: node35-relay-bin.000002
                Relay_Log_Pos: 285
        Relay_Master_Log_File: aminglinux1.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

解锁“主”上的表（在主上机器操作）

mysql> unlock table;
Query OK, 0 rows affected (0.00 sec)

测试主从

• 主上 mysql -uroot aming
• select count(*) from db;
• truncate table db;
• 到从上 mysql -uroot aming
• select count(*) from db;
• 主上继续drop table db;
• 从上查看db表

1. 在主上机器，进入到mysql