一 、公司网站单个 MySQL 难题浅析
在合作社网站中,后端 MySQL
数据库唯有一台时,会有以下难题:单点故障,服务不足
用;无法处理大批量的产出数据请求;数据丢失将导致大魔难。
改建格局:
日增 MySQL 数据库服务器,对数码举行备份,形成主备。
管教准备 MySQL 数据库服务器是一样的
主服务器宕机了,备份服务器继续工作,数据有保持
MySQL 主从复制与读写分离是周全相关

条件是原先布署lnmp+keepalived负载均衡的2台机器,IP分别是10.10.10.13和10.10.10.14.

主服务器  (mysql-master)  47.74.132.151 已安装mysql (内无多少)

示范一下在MySQL下搭建多主一从的经过。

澳门金沙国际 1

1,登录连串

从服务器 (mysql-slave)  47.92.107.42  已安装mysql (内无多少)

尝试环境:

            192.168.24.129:3306

           192.168.24.129:3307

           192.168.24.129:3308

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

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

一 发端布局 主服务器 47.74.132.151

主库操作

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

[root@hd3 ~]# netstat -nltp

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

导出数据

各自在3306和3307上导出须求的数据库。

澳门金沙国际 2

Active Internet connections (only servers)

[mysqld]

3306:

登录数据库:

[root@localhost 3306]# mysql -uroot -poldboy123 -S
/tmp/mysql3306.sock

锁表:

mysql> flush tables with read lock;

状态点:

mysql> show master status;

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

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000006 |      154 |             
|                  |                   |

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

1 row in set (0.00 sec)

另开窗口开首导数据:

[root@localhost tmp]# mysqldump -uroot -poldboy123
-S /tmp/mysql3306.sock -F -R -x –master-data=2 -A –events|gzip
>/tmp/dockerwy.sql.gz

在此查看情状点多少个要保持一致,否则表没有锁住

mysql> show master status;

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

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000007 |      154 |             
|                  |                   |

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

1 row in set (0.00 sec)

解锁表:

mysql> unlock tables;

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

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

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

3307:

登录3307数据库: 

[root@localhost 3307]# mysql -uroot -poldboy123 -S
/tmp/mysql3307.sock

锁表:

mysql>flush tables with read lock;

翻开状态点:

mysql> show master status;

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

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000007 |      154 |             
|                |                   |

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

1 row in set (0.00 sec)

另开窗口导数据:

[root@localhost 3307]# mysqldump -uroot
-poldboy123 -S /tmp/mysql3307.sock -F -R -x –master-data=2 -A
–events|gzip >/tmp/dockerwy_2.sql.gz

从新查看状态点:

mysql> show master status;

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

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000008 |      154 |             
|                  |                   |

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

1 row in set (0.00 sec)

解锁表:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

三、MySQL 主从备份案例

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

server-id=1 服务器ID

创造授权账号

独家在3306和3307地方建立授权账号

两台CentOS7服务器

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

重启mysql  (service mysqld start)

3306:

mysql> grant replication slave on *.* to
‘backup’@’192.168.24.129’ identified by ‘backup’;

master—192.168.1.46

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

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

3307:

mysql> grant replication slave on *.* to
‘backup’@’192.168.24.129’ identified by ‘backup’;

slave—192.168.1.47

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

mysql -uroot -p

从库操作

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

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

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)

修改从库存储形式

修改3308的master-info和relay-info格局,从文件存储改为表存储。

确马上间共同环境 , 在主服务器上安装配置 NTP  时间一起服务器

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

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

编写配置文件

[root@localhost 3308]# vim my.cnf

在[mysqld]模块下添加如下两行

master_info_repository=TABLE

relay_log_info_repository=TABLE

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

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

二 开首布局从劳动器 47.92.107.42

重启3308数据库:

[root@localhost 3308]# /data/3308/mysqld
restart

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

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

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

重启之后我们能够登录数据库查看;

[root@localhost 3308]# mysql -uroot -poldboy123 -S
/tmp/mysql3308.sock

mysql: [Warning] Using a password on the command
line interface can be insecure.

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

Your MySQL connection id is 3

Server version: 5.7.16 MySQL Community Server
(GPL)

Copyright (c) 2000, 2016, 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> show variables like
‘relay_log_info_repository’;

+—————————+——-+

| Variable_name             | Value |

+—————————+——-+

| relay_log_info_repository | TABLE |

+—————————+——-+

1 row in set (0.01 sec)

mysql> show variables like
‘master_info_repository’;

+————————+——-+

| Variable_name          | Value |

+————————+——-+

| master_info_repository | TABLE |

+————————+——-+

1 row in set (0.01 sec)

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

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

[mysqld]

导入数据

[root@master ~]# systemctl start ntpd

 

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

导入3306的数据:

[root@localhost 3308]# gzip -d
/tmp/dockerwy.sql.gz

[root@localhost 3308]# mysql -uroot -poldboy123 -S
/tmp/mysql3308.sock < /tmp/dockerwy.sql.

[root@master ~]# chkconfig ntpd on

注:主从机器mysql版本相同,并都已经起首化表并设置了root口令。

server-id=10 服务器ID

导入3307的数据:

[root@localhost 3308]# gzip -d
/tmp/dockerwy_2.sql.gz

[root@localhost 3308]# mysql -uroot -poldboy123 -S
/tmp/mysql3308.sock < /tmp/dockerwy_2.sql

从服务器上进行时间共同

 

重启mysql  (service mysqld start)

执行change master to

登录slave进行同步操作,分别change master两台服务器,前面以for channel
‘channel_name’区分

mysql> change master to
master_host=’192.168.24.129′,master_user=’backup’,master_port=3306,master_password=’backup’,master_log_file=’mysql-bin.000006′,master_log_pos=154
for channel ‘master_1’;

Query OK, 0 rows affected, 2 warnings (0.07
sec)

mysql> change master to
master_host=’192.168.24.129′,master_user=’backup’,master_port=3307,master_password=’backup’,master_log_file=’mysql-bin.000007′,master_log_pos=154
for channel ‘master_2’;

Query OK, 0 rows affected, 2 warnings (0.04
sec)

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

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

mysql -uroot -p

启动slave操作

可以透过start slave的艺术去启动所有的复制,也足以由此单个复制源的格局,上面介绍单个复制的的起步演示

mysql> start slave for channel ‘master_1’;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave for channel ‘master_2’;

Query OK, 0 rows affected (0.02 sec)

[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

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;

翻开同步状态

好端端启动后,可以查看同步的情事,执行show slave status for channel
‘channel_name\G’查看复制源master_1的一道状态;

mysql> show slave status for channel
‘master_1’\G

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

Slave_IO_State: Waiting for master to send
event

mysql主从复制,的主从复制。     Master_Host: 192.168.24.129

Master_User: backup

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 154

Relay_Log_File:
localhost-relay-bin-master_1.000006

Relay_Log_Pos: 367

Relay_Master_Log_File: mysql-bin.000008

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

Relay_Log_Space: 634

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

Master_UUID:
df233252-afd5-11e6-8070-000c2962d708

Master_Info_File: mysql.slave_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_1

Master_TLS_Version:

1 row in set (0.00 sec)

查看master_2的一起状态

mysql> mysql> show slave status for channel
‘master_2’\G

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

Slave_IO_State: Waiting for master to send
event

Master_Host: 192.168.24.129

Master_User: backup

Master_Port: 3307

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 154

Relay_Log_File:
localhost-relay-bin-master_2.000004

Relay_Log_Pos: 367

Relay_Master_Log_File: mysql-bin.000008

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

Relay_Log_Space: 634

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

Master_UUID:
49bf20e1-afe2-11e6-aef5-000c2962d708

Master_Info_File: mysql.slave_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_2

Master_TLS_Version:

1 row in set (0.00 sec)

根源为知笔记(Wiz)

[root@slave ~]# crontab -l

       [mysqld]

Query OK, 0 rows affected (0.01 sec)

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

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

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

配置 MySQL Master  主服务器

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

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

# service mysqld restart

检查中央同步,若是你看来Slave_IO_Running和Slave_SQL_Running均为Yes,则主从复制连接正常

在[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

 

mysql> create database mysqltest;

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

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

mysql> use mysqltest;

给从服务器授权

 #mysql -uroot -p  

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)

Enter password:

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

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

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

布署从服务器

Server version: 5.1.73-log Source distribution

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

[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 #大旨服务器的 server-id  不可以一如既往!

 

声明下自己配置的时候遇到的难题Slave_IO_Running 一贯为NO 自己的缘故是通讯相当服务器安全组配置没有计划好导致的通信败北

[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 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

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 status\G

*************************** 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>

 

相关文章