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

环境是先前配置lnmp+keepalived负载均衡的2台机器,IP分别是10.10.10.13和10.10.10.14.

www.512.net 1

1,登录系统

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

Last login: Sun Jul 24 23:36:55 2016 from 10.10.10.1

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

[root@hd3 ~]# netstat -nltp

www.512.net 2

Active Internet connections (only servers)

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

Proto Recv-Q Send-Q Local Address               Foreign
Address             State       PID/Program name  

三、MySQL 主从备份案例

tcp        0      0 127.0.0.1:9000             
0.0.0.0:*                   LISTEN      9471/php-fpm       

两台CentOS7服务器

tcp        0      0 0.0.0.0:3306               
0.0.0.0:*                   LISTEN      9362/mysqld        

master—192.168.1.46

tcp        0      0 0.0.0.0:80                 
0.0.0.0:*                   LISTEN      11021/nginx        

slave—192.168.1.47

tcp        0      0 0.0.0.0:22                 
0.0.0.0:*                   LISTEN      1961/sshd          

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

tcp        0      0 127.0.0.1:631              
0.0.0.0:*                   LISTEN      1821/cupsd         

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

tcp        0      0 127.0.0.1:25               
0.0.0.0:*                   LISTEN      2115/master        

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

tcp        0      0 :::22                       :::* 
                      LISTEN      1961/sshd          

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

tcp        0      0 ::1:631                    
:::*                        LISTEN      1821/cupsd         

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

tcp        0      0 ::1:25                     
:::*                        LISTEN      2115/master

[root@master ~]# systemctl start ntpd

 

[root@master ~]# chkconfig ntpd on

注:主从机器mysql版本一致,并都已经初始化表并设置了root口令。

从服务器上进行时间同步

 

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

2、修改主从服务器配置文件的2个字段

[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

 #vi /etc/my.cnf

[root@slave ~]# crontab -l

       [mysqld]

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

       log-bin=mysql-bin   //启用二进制日志

配置 MySQL Master  主服务器

       server-id=1     
//服务器唯一ID,默认是1,这里主的用的默认,从服务器改为2。之后 

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

# service mysqld restart

在[mysqld]模块下手动添加

Stopping mysqld:  [  OK  ]

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

Starting mysqld:  [  OK  ]

[root@master ~]# systemctl restart mariadb

 

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

3,在主服务器上建立帐户并授权slave:

给从服务器授权

 #mysql -uroot -p  

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

Enter password:

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

Welcome to the MySQL monitor.  Commands end with ; or g.

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

Your MySQL connection id is 2

配置从服务器

Server version: 5.1.73-log Source distribution

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

 

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

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights
reserved.

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

 

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

Oracle is a registered trademark of Oracle Corporation and/or its

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)

affiliates. Other names may be trademarks of their respective

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

owners.

MariaDB [(none)]> show slave statusG;
*************************** 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

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input
statement.

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

 

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

 mysql>GRANT REPLICATION SLAVE ON *.* to ‘sync’@’%’ identified by
‘123456’;

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

查询master的状态,记录下 FILE Position 的值

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

mysql> show master status;

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

+——————+———-+————–+——————+

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

+——————+———-+————–+——————+

 

| mysql-bin.000002 |      251 |              |                  |

注意:

+——————+———-+————–+——————+

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

1 row in set (0.00 sec)

 

4,配置从服务器

# mysql -uroot -p         

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 2

Server version: 5.1.73-log Source distribution

 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights
reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input
statement.

 

mysql> change master to
master_host=’10.10.10.13′,master_user=’sync’,master_password=’123456′,master_log_file=’my

sql-bin.000002′,master_log_pos=251;

Query OK, 0 rows affected (0.03 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave statusG

*************************** 1. row
***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.10.10.13

                  Master_User: mysync

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 251

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000002

             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: 251

              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)

关键是Slave_IO及Slave_SQL进程一定要都YES

 

 

5,主从复制测试

先在主mysql上:

mysql> create database fuck;

Query OK, 1 row affected (0.03 sec)

 

mysql> use fuck;

Database changed

mysql>

mysql>

mysql> create table fuck(id int(3),name char(10));

Query OK, 0 rows affected (0.08 sec)

 

mysql>

mysql>

mysql> insert into fuck values(001,’abcd’);

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| fuck               |

| mysql              |

| test               |

+——————–+

4 rows in set (0.03 sec)

 

mysql>

 

再登录从mysql,主服务器上新增的数据都出来了

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| fuck               |

| mysql              |

| test               |

+——————–+

4 rows in set (0.02 sec)

 

mysql> use fuck;

Database changed

mysql> select * from fuck;

+——+——+

| id   | name |

+——+——+

|    1 | abcd |

+——+——+

1 row in set (0.00 sec)

 

mysql>