[番外篇]MySQL多实例搭建主从同步

 黑渐神|he-jason.com   2018-11-23 21:22   1,367 人阅读  [番外篇]MySQL多实例搭建主从同步已关闭评论

MySQL多实例主从同步

接上一个[番外篇]
在做的多实例的基础上,把3308和3309做成3307的从库

主库

开启主库的二进制日志

[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
#开启了二进制日志文件
log-bin=/data/3307/mysql-bin
#这里也开启了server_id,同时从库ID不等于主库ID即可
server_id=7
port=3307
[client]
socket=/data/3307/mysql.sock

从以上配置文件我们可以直接授权从库的用户及主机即可

实例:mysql3307

#因为这几台实例在同一台主机上,所以这里可以用localhost,反之就需要授权IP
mysql> grant replication slave on *.* to slave@'localhost' identified by '123';
mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      919 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
#记住上面的FILE 值 和Position值,待会我们需要在从库上使用这两个值
##备份主数据库
bash> mysqldump -uroot -p3307 -S /data/3307/mysql.sock -A >/root/3307.sql

从库

从库和主库除了ID及数据目录不一样,其它都一样,这里就不操作配置文件了

bash> mysql -uroot -p3308 -S /data/3308/mysql.sock </root/3307.sql
bash> mysql3308
mysql> change master to
    -> master_host='localhost',
    -> master_port=3307,
    -> master_user='slave',
    -> master_password='123',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=919;#这里结束了,记得用分号
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 586
               Relay_Log_File: db01-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes #这里Yes就OK
            Slave_SQL_Running: Yes #这里也Yes 就说明主从成功搭建.
#按照上面的方法,我们把3309实例也配置上,这里我就不复制粘贴了。
#如果没有出现两个Yes的话,一定要保持从库与主库的数据一致后,再开始做主从,重新写入change master to 的时候,记得stop slave;
#好了,接下来我们就要验证主库更新数据,从库是否跟着一起更新,是否同步。come on!

验收主从同步

主库添加一个数据库

mysql> create database student char set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| test               |
+--------------------+
5 rows in set (0.00 sec)

从库检查student数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| test               |
+--------------------+
5 rows in set (0.00 sec)

主库添加一个数据表

mysql> use student
Database changed
mysql> create table stu(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student.stu values(1);
Query OK, 1 row affected (0.01 sec)

从库验收数据表和表内容

###3308实例
mysql> show tables from student;
+-------------------+
| Tables_in_student |
+-------------------+
| stu               |
+-------------------+
1 row in set (0.00 sec)
###内容这里出来了
mysql> select * from student.stu;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
### 证明一下这是3308端口的server_id=8的实例
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 8     |
+---------------+-------+
1 row in set (0.00 sec)
###3309实例
mysql> select * from student.stu;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 9     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show tables from student;
+-------------------+
| Tables_in_student |
+-------------------+
| stu               |
+-------------------+
1 row in set (0.00 sec)

以上多实例主从同步的搭建就到此大功告成啦。期待一期的番外,主变无,从变主。

本文地址:https://www.he-jason.com/dbskill/mysql/544.html
版权声明:本文为原创文章,版权归 黑渐神|he-jason.com 所有,欢迎分享本文,转载请保留出处!
双11拉新分百万红包

评论已关闭!