mysql multi主从复制_一.Mysql主从复制配置

最新推荐文章于 2022-07-31 21:56:53 发布

郝福莱

最新推荐文章于 2022-07-31 21:56:53 发布

阅读量77

点赞数

文章标签： mysql multi主从复制

本文链接：https://blog.csdn.net/weixin_30298733/article/details/113432156

版权

一.启动3台mysql服务器

[mysql@iZ25ufmpy4sZ ~]$ mysqld_multi start

[mysql@iZ25ufmpy4sZ~]$ mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

二.登录Master主服务器mysql3306

[mysql@iZ25ufmpy4sZ ~]$ mysql -u root -S /tmp/mysql3306.sock -p

Enter password:

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

Your MySQL connectionid is 3Server version:5.7.15-log MySQL Community Server (GPL)

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 clearthe current input statement.

mysql>

三.在Master主服务器mysql3306上建立账户并授权Slave

mysql> grant replication slave on *.* to 'mysqlsync'@'%' identified by 'mysqlsync';

Query OK,0 rows affected, 1 warning (0.00 sec)

mysql> show master status;

+----------------------+----------+--------------+------------------+-------------------+

+----------------------+----------+--------------+------------------+-------------------+

| mysql3306_bin.000006 | 442 | | | |

+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

四.登录Slave从服务器mysql3307或mysql3308

[mysql@iZ25ufmpy4sZ ~]$ mysql -u root -S /tmp/mysql3307.sock -p

Enter password:

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

Your MySQL connectionid is 5Server version:5.7.15-log MySQL Community Server (GPL)

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 clearthe current input statement.

mysql>

五.配置Slave从服务器mysql3307或mysql3308

mysql> change master to master_host='127.0.0.1',master_user='mysqlsync',master_password='mysqlsync',master_log_file='mysql3306_bin.000006',master_log_pos=442;

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

mysql>start slave;

Query OK,0 rows affected (0.03sec)

mysql>

六.在Slave从服务器上查看slave状态

mysql> mysql>show slave status;+----------------------------------+-------------+-------------+-------------+---------------+----------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+

+----------------------------------+-------------+-------------+-------------+---------------+----------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+

| Waiting for master to send event | 127.0.0.1 | mysqlsync | 3306 | 60 | mysql3306_bin.000006 | 442 | iZ25ufmpy4sZ-relay-bin.000002 | 324 | mysql3306_bin.000006 | Yes | Yes | | | | | | | 0 | | 0 | 442 | 538 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 3306 | 526196c9-dbe6-11e6-aa52-00163e0066e2 | /home/mysql/mysql3307/data/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | |

1 row in set (0.00 sec)

七.主从服务器配置测试

在Master主服务器上新建一个database,则Slave从服务器上应该也能够查询到，如下

---------------一下为主服务器

[mysql@iZ25ufmpy4sZ~]$ mysql -u root -S /tmp/mysql3306.sock -p

Enter password:

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

Your MySQL connectionid is 15Server version:5.7.15-log MySQL Community Server (GPL)

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 clearthe current input statement.

mysql>create database test;

Query OK,1 row affected (0.00sec)

mysql>

--------------从服务器

[mysql@iZ25ufmpy4sZ ~]$ mysql -u root -S /tmp/mysql3307.sock -p

Enter password:

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

Your MySQL connection id is 13

Server version: 5.7.15-log MySQL Community Server (GPL)

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

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

+--------------------+

5 rows in set (0.00 sec)

mysql>

问题：1.This operation cannot be performed with a running slave io thread; run STOP SLAV

这是由于我在还没有停止slave进程的时候，又执行start slave。故要先运行stop slave,

郝福莱

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql multi主从复制_一.Mysql主从复制配置

一.启动3台mysql服务器[mysql@iZ25ufmpy4sZ ~]$ mysqld_multi start[mysql@iZ25ufmpy4sZ~]$ mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld33...
复制链接

扫一扫