澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

澳门新浦京娱乐场网站:的主从复制,1陆多源复

一 、集团网址单个 MySQL 难点浅析
在店堂网址中,后端 MySQL 数据库只有一台时,会有以下难点:单点故障,服务不足
用;不可能管理大批量的出现数据请求;数据丢失将导致大灾殃。
改建格局:
充实 MySQL 数据库服务器,对数据开始展览备份,变成主备。
确认保障图谋 MySQL 数据库服务器是一样的
主服务器宕机了,备份服务器继续做事,数据有保险
MySQL 主从复制与读写分离是细心相关

主服务器  (mysql-master)  四柒.7四.13二.15一 已设置mysql (内无数据)

示范一下在MySQL下搭建多主一从的进度。

MySQL主从故障修复

澳门新浦京娱乐场网站 1

从服务器 (mysql-slave)  四7.92.⑩柒.42  已设置mysql (内无多少)

试验景况:

            192.168.24.129:3306

           192.168.24.129:3307

           192.168.24.129:3308

测试库:
192.168.1.2 主
192.168.1.3 从

1台主服务器能够做八个从服务器,从服务器也足以充当主服务器,再做多个从服务器,由此及彼....

一 开头安插 主服务器 四七.7四.13二.15一

主库操作

192.168.1.4 主 4又是2的从库
192.168.1.5 从

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

壹.配置从前先查看/etc/my.cnf

导出数据

个别在330陆和3307上导出须求的数据库。

有人修改了192.16八.一.贰和1玖二.168.一.叁的数据库参数后,重启数据库。
遗忘了1九贰.16八.1.四又是1玖二.16八.1.二的从库,导致1玖二.168.壹.二和1九2.168.一.四的基本断掉。
而且在19二.16捌.壹.二上创立了新库还原数据删除等操作,导致1九2.168.1.4提醒错误。

澳门新浦京娱乐场网站 2

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

宪章如下:
经过从库查看主从状态:
mysql> show slave statusG  
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.33.243
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 303
               Relay_Log_File: relay-log.000005
                Relay_Log_Pos: 340
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1049
                   Last_Error: Error 'Unknown database 'sakila'' on query. Default database: 'sakila'. Query: 'create table sakila (id int,name varchar(2))'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194
              Relay_Log_Space: 745
              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_澳门新浦京娱乐场网站:的主从复制,1陆多源复制。SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1049
               Last_SQL_Error: Error 'Unknown database 'sakila'' on query. Default database: 'sakila'. Query: 'create table sakila (id int,name varchar(2))'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 360360
1 row in set (0.00 sec)

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

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)

笔录下二进制日志的和点的任务:
Relay_Master_Log_File: mysql-bin.000006
Exec_Master_Log_Pos: 194

三、MySQL 主从备份案例

server-id=1 服务器ID

确立授权账号

分别在3306和3307方面创建授权账号

停掉从库的slave
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

两台CentOS7服务器

重启mysql  (service mysqld start)

3306:

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

并在主库删除掉新建的库
mysql> drop database sakila;
Query OK, 1 row affected (0.04 sec)

master---192.168.1.46

二.登入mysql,在mysql中增多一个backup的账号,并授权给从服务器。

3307:

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

再次举办主从复制
mysql> show master status;
------------------ ---------- -------------- ------------------
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
------------------ ---------- -------------- ------------------
| mysql-bin.000006 |      388 |              |                  |
------------------ ---------- -------------- ------------------
1 row in set (0.00 sec)

slave---192.168.1.47

mysql -uroot -p

从库操作

mysql> change master to master_host='192.168.1.2',master_port=3306,master_user='master',master_password='123456',master_log_file='mysql-bin.000006',master_log_pos=388;
Query OK, 0 rows affected (0.01 sec)

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

mysql>grant replication slave on *.* to 'backup'@'1九贰.168.4八.130' identified by 'backup'; 成立backup用户,并授权给1玖2.16八.4八.130应用。
Query OK, 0 rows affected (0.00 sec)

修改从仓库储存款和储蓄办法

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

mysql> start slave ;
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@localhost 3308]# vim my.cnf

在[mysqld]模块下增多如下两行

master_info_repository=TABLE

relay_log_info_repository=TABLE

查阅主从状态还提示原本的荒唐,使用sql_slave_skip_counter参数
mysql> show variables like  '%skip%';
------------------------ -------
| Variable_name          | Value |
------------------------ -------
| skip_external_locking  | ON    |
| skip_name_resolve      | ON    |
| skip_networking        | OFF   |
| skip_show_database     | OFF   |
| slave_skip_errors      | OFF   |
| sql_slave_skip_counter | 0     |
------------------------ -------
6 rows in set (0.00 sec)

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

2 起初安排从服务器 47.九二.拾7.4二

重启3308数据库:

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

默认为0

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

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

重启之后大家能够登入数据库查看;

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

mysql> set global sql_slave_skip_counter = 1;

22 server 1二七.1二七.一.0 // 手动增加此两行内容
23 fudge 127.127.1.0 startum 8

[mysqld]

导入数据

mysql> start slave;

[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

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> set global sql_slave_skip_counter = 1;

从服务器上举行时间共同

重启mysql  (service mysqld start)

执行change master to

澳门新浦京娱乐场网站:的主从复制,1陆多源复制。登入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)

mysql> start slave;

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

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)

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.2
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 388
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           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: 388
              Relay_Log_Space: 403
              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: 360360
             
             
末段在192.16八.1.贰上海重机厂复建库并回涨数据

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

查看同步状态

常规运营后,能够查阅同步的事态,实践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_二的共同状态

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

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

反省宗旨同步,如若你收看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 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

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

本文由澳门新浦京娱乐场网站发布于澳门新浦京娱乐场网站,转载请注明出处:澳门新浦京娱乐场网站:的主从复制,1陆多源复