mysql 复制搭建_mysql主从复制搭建

实验架构：192.168.0.92mysql 主192.168.0.93mysql 从1、环境配置请参照: https://www.cnblogs.com/effortsing/p/10367025.html2、mysql安装 请参照：https://www.cnblogs.com/effortsing/p/9982028.html3、Mysql主从同步环境部署---------主服务器操作记录----------在my.cnf文件的[mysqld]配置区域添加下面内容：

[root@master1~]#vim /etc/my.cnf

server-id = 1log-bin = mysql-bin

sync_binlog= 1binlog_checksum=none

binlog_format=mixed

auto-increment-increment = 2auto-increment-offset = 1slave-skip-errors =all

重启数据库

systemctl restart mysqld

授权从服务器同步权限

mysql> grant replication slave on *.* to 'root'@'192.168.0.%' identified by 'jenkins@123';

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

mysql>flush privileges;

Query OK, 0 rows affected (0.00sec)

查看当前的binlog以及数据所在位置

mysql>show master status;+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000006 | 996 | | | |

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

1 row in set (0.00sec)---------------从服务器做同步操作---------------在my.cnf文件的[mysqld]配置区域添加下面内容：

server-id = 2重启数据库

systemctl restart mysqld

下面开始同步主数据库中的数据

先停止Slave

mysql>stop slave;

Query OK, 0 rows affected (0.01sec)

然后连接Master

mysql> change master to master_host='192.168.0.92',master_user='root',master_password='jenkins@123',master_log_file='mysql-bin.000006',master_log_pos=150;

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

再启动Slave

mysql>start slave;

Query OK, 0 rows affected (0.01sec)

查看两个线程状态是否为YES

mysql>show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

以上表明双方已经实现了mysql主从同步。

测试主从效果

在主数据库上写入新数据

mysql>unlock tables;

Query OK, 0 rows affected (0.00sec)

创建三个数据库：db1、db2、db3

mysql>create database db1;

Query OK,1 row affected (0.01sec)

mysql>create database db2;

Query OK,1 row affected (0.01sec)

mysql>create database db3;

Query OK,1 row affected (0.01sec)

给数据库授权，否则程序时无法连接db1数据库的

mysql> grant all privileges on db1.* TO 'root'@'%' identified by 'jenkins@123'with grant option;

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

mysql> grant all privileges on db2.* TO 'root'@'%' identified by 'jenkins@123'with grant option;

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

mysql> grant all privileges on db3.* TO 'root'@'%' identified by 'jenkins@123'with grant option;

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

mysql>flush privileges;

Query OK, 0 rows affected (0.00sec)

db1数据库创建1个表：tb1

mysql>use db1;

Database changed

mysql> create table if not exists tb1 ( id int(10) PRIMARY KEY AUTO_INCREMENT, name varchar(50) NOT NULL);

Query OK, 0 rows affected (0.04sec)

向db1数据库中写入数据

mysql> insert into tb1 values(2,'join');

Query OK,1 row affected (0.00sec)

mysql> insert into tb1 values(1,'bob');

Query OK,1 row affected (0.00sec)

mysql> select * fromtb1;+----+-----------+

| id | name |

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

| 1 | bob |

| 2 | join |

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

2 rows in set (0.00sec)

然后在从数据库上查看，发现数据已经同步过来了!

mysql> select * fromdb1.tb1;+----+-----------+

| id | name |

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

| 1 | bob |

| 2 | join |

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

2 rows in set (0.00sec)

最后在主库上删除表，以免影响读写分离实验

mysql>drop table tb1;

Query OK, 0 rows affected (0.01sec)

至此，Mysql主从同步环境已经实现。