手把手教你MySQL服务器主从复制同步---------超详细！

一、主从复制原因与解决

1.1 原因

■在企业网站中，后端MySQL数据库只有一台时，会有以下问题：
●遇到单点故障，服务不可用
●无法处理大量的并发数据请求
●数据丢失将会造成很大损失

1.2 解决方法

■增加MySQL数据库服务器，对数据进行备份，形成主备
■确保主备MySQL数据库服务器数据是一样的
■主服务器宕机了，备份服务器继续工作，数据有保障
■MySQL主从复制与读写分离是密切相关的

1.3 MySQL主从复制的类型

■基于语句的复制（默认）
●在主服务器上执行的语句，从服务器执行同样的语句
■基于行的复制
●把改变的内容复制到从服务器
■混合类型的复制
●一旦发现基于语句无法精确复制时，就会采用基于行的复制

二、主从复制实验实操

2.1 建立时间同步环境

■在主服务器上安装配置NTP时间同步服务器（20.0.0.10）

[root@localhost~]# yum -y install ntp
[root@localhost~]# vi /etc/ntp.conf  
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst
'//找到上面四行，删除，然后添加下面两行，保存退出'
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@localhost~]# systemctl restart ntpd
[root@localhost~]# systemctl enable ntpd

■在从服务器1上配置NTP同步（20.0.0.11）

[root@localhost~]# yum -y install ntpdate
[root@localhost~]# ntpdate 20.0.0.10
[root@localhost~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.10 >>/var/log/nt
pdate.log
[root@localhost~]# systemctl restart ntpdate
[root@localhost~]# systemctl enable ntpdate
[root@localhost~]# tail -f /var/log/ntpdate.log

■在从服务器2上配置NTP同步（20.0.0.12）

[root@localhost~]# yum -y install ntpdate
[root@localhost~]# ntpdate 20.0.0.10
[root@localhost~]# crontab -e   
   '//添加计划任务'
*/2 * * * * /usr/sbin/ntpdate 20.0.0.10 >>/var/log/ntpdate.log
[root@localhost~]# systemctl restart ntpdate
[root@localhost~]# systemctl enable ntpdate
[root@localhost~]# tail -f /var/log/ntpdate.log

2.2 主服务器、从服务器1、从服务器2均安装MySQL服务

[root@localhost~]# yum -y install \
ncurses \
ncurses-devel \
bison \
cmake \
gcc \
gcc-c++
[root@localhost~]# useradd -s /sbin/nologin  mysql
[root@localhost~]# cd /opt
[root@localhost opt]# tar xf mysql-boost-5.7.20.tar.gz
[root@localhost opt]# cd /opt/mysql-5.7.20/
[root@localhost mysql-5.7.20]# 
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
[root@localhost mysql-5.7.20]# make -j4
[root@localhost mysql-5.7.20]# make install

[root@localhost mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/  
 '//数据库目录进行权限调整'

[root@localhost mysql-5.7.20]# vi /etc/my.cnf     
 '//建立调整配置文件'
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

[root@localhost mysql-5.7.20]# chown mysql:mysql /etc/my.cnf
[root@localhost mysql-5.7.20]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost mysql-5.7.20]# echo 'export PATH' >> /etc/profile  
     '//设置环境变量'
[root@localhost mysql-5.7.20]# source /etc/profile    
'//生效'

[root@localhost mysql-5.7.20]# cd /usr/local/mysql/

[root@localhost mysql]# bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@localhost mysql]# systemctl enable mysqld
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld
[root@localhost mysql]# netstat -anpt | grep 3306
[root@localhost mysql]# mysqladmin -u root -p password
'//设置密码'

2.3 主服务器配置

[root@localhost~]#  vi /etc/my.cnf
server-id = 11   '//修改成11'
log_bin = master-bin
log-slave-updates = true   '//在下方添加这两行'

[root@localhost~]# systemctl restart mysqld

[root@localhost~]# mysql -uroot -p

mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by 'abc123';

mysql> flush privileges;

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

2.4 从服务器1配置

[root@localhost mysql]# vi /etc/my.cnf
server-id = 22  '//修改成22'
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index    '//在下方添加这两行'
[root@localhost mysql]# systemctl restart mysqld
[root@localhost mysql]# mysql -uroot -p
mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=599;
'//这里的master-bin.000001和master_log_pos=599要和主服务器的数据库信息一致，不一致要先stop slave，然后更改同步信息'
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.0.0.10
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 758
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 480
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
. . . . . . . . . . . . . . . . '//省略部分内容'

2.5 从服务器2配置

[root@localhost mysql]# vi /etc/my.cnf
server-id = 33  '//修改成33'
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index   '//在下方添加这两行'
[root@localhost mysql]# systemctl restart mysqld
[root@localhost mysql]# mysql -uroot -p

mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=599;
'//这里的master-bin.000001和master_log_pos=599要和主服务器的数据库信息一致，不一致要先stop slave，然后更改同步信息'
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.0.0.10
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 758
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 480
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            . . . . . . . . . . . . . . '//省略部分内容'

2.6 验证

■主服务器：

[root@localhost mysql]# mysql -uroot -p
mysql> create database text;  
'//创建数据库text'
Query OK, 1 row affected (0.00 sec)

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

■从服务器1上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+
5 rows in set (0.00 sec)
'//发现存在text数据库'

■从服务器2上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+
5 rows in set (0.00 sec)
'//发现存在text数据库'