操作系统

当前位置:金沙棋牌 > 操作系统 > mysql主从复制,的主从复制

mysql主从复制,的主从复制

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-09 19:49

一 、企业网站单个 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_nameG’查看复制源master_1的同步状态;

mysql> show slave status for channel 'master_1'G

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

Slave_IO_State: Waiting for master to send event

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

 

本文由金沙棋牌发布于操作系统,转载请注明出处:mysql主从复制,的主从复制

关键词:

上一篇:没有了

下一篇:没有了