MySQL主从

主从复制原理：

从库生成两个线程，一个i/o线程，一个SQL线程；
i/o线程去请求主库的binlog，并且得到的binlog日志写到relay log(中继日志)文件中，
主库会生成一个log dump线程，用来给从库的i/o线程传binlog；
SQL线程，会读取中继日志文件，解析成具体的操作并执行，从而达到主从数据库数据同步。

环境说明：

数据库角色	IP	系统版本与应用	有无数据
主数据库	192.168.225.128	redhat7 mysql-5.7	有数据
从数据库	192.168.225.129	redhat7 mysql-5.7	无数据

MySQL主从配置

//在主中创建一个库，并查看
mysql> create database hxd;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hxd                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.31 sec)

//再查看从库中有哪些库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

//全备主库
//全备主库时
需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一样
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

//此锁表的终端必须在备份完成后才能退出
[root@hxdserver ~]# mysqldump -uroot -phxd123456. --all-databases > /opt/all-201811091118.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@hxdserver ~]# scp /opt/all-201811091118.sql root@192.168.225.129:/opt/
root@192.168.225.129's password: 
all-201811091118.sql                          100%  783KB  68.1MB/s   00:00

//解除主库的锁表状态，直接退出交互式界面即可
mysql> exit
Bye

//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@localhost ~]# mysql -uroot -phxd123456. < /opt/all-201811091118.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -phxd123456. -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hxd                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

• 在两台数据库都没有数据或数据完全相同的情况下，直接做以下操作
• 在主数据库里创建一个同步账号授权给从库使用

mysql> create user 'repl'@'192.168.225.129' identified by 'repl123.';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.225.129';
Query OK, 0 rows affected (0.00 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

• 配置主数据库

[root@hxdserver ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin         //启用binlog日志
server-id=1            //数据库服务器唯一标识符，主库的id值必须比从库的小
//重启服务
[root@hxdserver ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 配置从数据库

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=2   //设置从库的唯一标识符，从库的id值必须比主库大
relay-log=mysql-relay-bin //启用中继日志relay-log

//重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL... SUCCESS! 


//在从数据库配置并开启主从复制
mysql> change master to
    -> master_host='192.168.225.128',
    -> master_user='repl',
    -> master_password='repl123.',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

//master_log_file='主库状态中的File'
//master_log_pos='主库状态中的Position'
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.225.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes                     //必须为yes
            Slave_SQL_Running: Yes                  //必须为yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 2092b848-e3f4-11e8-85e8-000c294b7cb8
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

• 验证
• 在主数据库的hxd库中创建表并插入数据

mysql> use hxd;
Database changed
mysql> create table student(id int not null,name varchar(10) not null,age tinyint);
Query OK, 0 rows affected (0.04 sec)

mysql> insert student values(1,'tom',15),(2,'jerry',25);
Query OK, 2 rows affected (1.67 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   15 |
|  2 | jerry |   25 |
+----+-------+------+
2 rows in set (0.00 sec)

//在从数据库查看数据是否同步了
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hxd                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hxd;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_hxd |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   15 |
|  2 | jerry |   25 |
+----+-------+------+
2 rows in set (0.00 sec)