必发88官网 2

的主从复制,mysql主从复制

一 、企业网站单个 MySQL 问题分析
在企业网站中,后端 MySQL
数据库只有一台时,会有以下问题:单点故障,服务不可
用;无法处理大量的并发数据请求;数据丢失将造成大灾难。
改造办法:
增加 MySQL 数据库服务器,对数据进行备份,形成主备。
确保准备 MySQL 数据库服务器是一样的
主服务器宕机了,备份服务器继续工作,数据有保障
MySQL 主从复制与读写分离是密切相关

主服务器  (mysql-master)  47.74.132.151 已安装mysql (内无数据)

必发88官网 1

从服务器 (mysql-slave)  47.92.107.42  已安装mysql (内无数据)

一台主服务器可以做多个从服务器,从服务器也可以充当主服务器,再做多个从服务器,以此类推….

一 开始配置 主服务器 47.74.132.151

二、主从复制原理原理
MySQL 主从复制的类型:
·基于语句的复制
·基于行的复制
·混合类型的复制

1.配置之前先查看/etc/my.cnf

必发88官网 2

[mysqld]

1、MySQL 从服务器开启 I/O
线程,向主服务器请求数据同步(获取二进制日志)
2、MySQL 主服务器开启 I/O 线程回应从服务器
3、从服务器得到主的二进制日志写入中继日志
4、从服务器开启 SQL 线程将日志内容执行,实现数据同步

log-bin=mysql-bin 启动二进制文件

三、MySQL 主从备份案例

server-id=1 服务器ID

两台CentOS7服务器

重启mysql  (service mysqld start)

master—192.168.1.46

2.登录mysql,在mysql中添加一个backup的账号,并授权给从服务器。

slave—192.168.1.47

mysql -uroot -p

所有服务器关闭防火墙、关闭selinux

mysql>grant replication slave on *.* to ‘backup’@’192.168.48.130’
identified by ‘backup’; 创建backup用户,并授权给192.168.48.130使用。
Query OK, 0 rows affected (0.00 sec)

建立时间同步环境 , 在主服务器上安装配置 NTP  时间同步服务器

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

[root@master ~]# yum -y install ntp

二 开始配置从服务器 47.92.107.42

[root@master ~]# vim /etc/ntp.conf

确保/etc/my.cnf中有log-bin=mysql-bin和server-id=1参数,并把server-id=1修改为server-id=10。修改之后如下所示:

22 server 127.127.1.0 // 手动添加此两行内容
23 fudge 127.127.1.0 startum 8

[mysqld]

[root@master ~]# systemctl start ntpd

log-bin=mysql-bin 启动二进制文件

[必发88官网,root@master ~]# chkconfig ntpd on

server-id=10 服务器ID

从服务器上进行时间同步

重启mysql  (service mysqld start)

[root@slave ~]# yum -y install ntpdate

mysql -uroot -p

[root@slave ~]# ntpdate 192.168.1.46
23 Aug 17:50:14 ntpdate[3407]: adjust time server 192.168.1.46 offset
0.000029 sec
[root@slave ~]# crontab -e

change master to
master_host=’47.74.132.151′,master_user=’backup’,master_password=’backup’,master_log_file=’mysql-bin.000001′,master_log_pos=261;

[root@slave ~]# crontab -l

Query OK, 0 rows affected (0.01 sec)

*/5 * * * * /usr/sbin/ntpdate 192.168.1.46

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

配置 MySQL Master  主服务器

mysql>  show slave status\G
*************************** 1. row
***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 47.74.132.151
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 261
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
            
Slave_IO_Running: Yes
           
Slave_SQL_Running: 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: 261
              Relay_Log_Space: 407
              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:
1 row in set (0.00 sec)

[root@master ~]# vim /etc/my.cnf

检查主从同步,如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常

在[mysqld]模块下手动添加

三 开始验证

10 log-bin=mysql-bin
11 log-slave-updates=true #手动添加,开启从日志
12 server-id=11

在主数据库上进行插入数据

[root@master ~]# systemctl restart mariadb

mysql> create database mysqltest;

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

mysql> use mysqltest;

给从服务器授权

mysql> create table user(id int(5),name char(10));

MariaDB [(none)]> grant replication slave on *.* to
‘myslave’@’192.168.1.%’ identified by ‘123123’;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values (00001,’zhangsan’);

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

登录从数据库查看此数据是否存在

MariaDB [(none)]> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 613 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| mysqltest          |
+——————–+
3 rows in set (0.00 sec)

配置从服务器

use mysqltest
mysql> select * from user;
+——+———-+
| id   | name     |
+——+———-+
|    1 | zhangsan |

[root@slave ~]# yum -y install mariadb-*

复制成功

[root@slave ~]# vim /etc/my.cnf

 

10 relay-log=relay-log-bin
11 relay-log-index=slave-relay-bin.index
12 server-id=12 #主从服务器的 server-id  不能相同!

注释下自己配置的时候遇到的问题
Slave_IO_Running 一直为NO 自己的原因是通信不行
服务器安全组配置没有配置好导致的通信失败

[root@slave ~]# systemctl restart mariadb
[root@slave ~]# mysqladmin -uroot password 123123
[root@slave ~]# mysql -uroot -p123123

 

MariaDB [(none)]> change master to
-> master_host=’192.168.1.46′, 
-> master_port=3306,
-> master_user=’myslave’,
-> master_password=’123123′,
-> master_log_file=’mysql-bin.000001′,
-> master_log_pos=613;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.46
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 613
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: 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: 613
Relay_Log_Space: 821
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: 11
1 row in set (0.00 sec)

通过查看 slave  状态,确保 Slave_IO_Running: Yes Slave_SQL_Running:
Yes

在 MySQL  主服务器创建 test db数据库

[root@master ~]# mysql -uroot -p123123

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+——————–+
5 rows in set (0.00 sec)

在从服务器查看是否数据同步

[root@slave ~]# mysql -uroot -p123123

MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+——————–+
5 rows in set (0.00 sec)

从服务器复制了主服务器上的数据库,主从复制成功。

 

注意:

若在 从服务器 start slave; 之后发现 Slave_IO_Running: No
、Slave_SQL_Running: Yes ,则
需要先 stop slave; 重新 change master to …; 再 start slave;