mysql主从复制

基于POS的数据库的主从复制

1.在mysql11和mysql2上安装数据库相关软件

[root@mysql1 mysql5.7.24]# ls
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm
[root@mysql2 mysql5.7.24]# yum install * -y

2.编辑配置文件

编辑mysql1的/etc/my.cnf文件

log-bin=mysql-bin
server-id=1(两台mysql配置不一样)

编辑mysql2的/etc/my.cnf文件

server-id=2

3.mysql1和mysql2开启数据库，查找临时密码，进行安全初始化设置密码

[root@server1 ~]# systemctl start mysqld
[root@server1 ~]# cat /var/log/mysqld.log |grep password
[root@server1 ~]# mysql_secure_installation

 

4.在mysql1数据库中创建repl用户，允许172.25.60.%的客户端可以通过repl用户远程连接172.25.60.1这个数据库
mysql1:

mysql> grant replication slave on *.* to repl@'172.25.60.%' identified by 'Mysql60+1';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      447 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

查看日志内容需要通过mysqlbinlog进行查看，否则查看到的日志是乱码

[root@mysql1 mysql]# mysqlbinlog  mysql-bin.000005

5.server2测试远程登录：

[root@mysql2 ~]# mysql -h 172.25.60.1 -u repl -p
Enter password:

 

6.登录mysql2配置从数据库：

mysql> change master to master_host='172.25.60.1',master_user='repl',master_password='Mysql60+1',master_log_file='mysql-bin.000005',master_log_pos=447;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

#启动slave

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

查看IO线程和SQL线程的状态

mysql> show slave status\G;

 

测试：
在master数据库中创建数据库test,并在test中创建uesrlist表，在表中插入数据：position-id发生改变(当数据库内容变动的时)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table userlist(
    -> username varchar(10),
    -> password varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | YES  |     | NULL    |       |
| password | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into userlist values('user1','111');
Query OK, 1 row affected (0.02 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

查看master的状态：偏移量发生变化，说明有新的

在slave数据库了可以同步过来

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

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| userlist       |
+----------------+
1 row in set (0.00 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user     | 111      |
+----------+----------+
1 row in set (0.00 sec)

 

基于gtid的数据库的主从复制

1.编辑sever1和server2的/etc/my.cnf文件，分别在文件中加入下面的内容

gtid_mode=ON     #开启gtid模式
enforce-gtid-consistency=true    #配置该参数，当master宕机后，从库提升为主，写操作自动迁移到从库上

2.在server2上配置

mysql> change master to master_host='172.25.60.1',master_user='repl',master_password='Mysql60+1',master_auto_position=1

mysql> start slave;

查看IO线程和SQL线程的运行状态

 

测试：在master上插入数据，slave上可以复制过来

server1:

mysql> insert into userlist values('user2','222');
Query OK, 1 row affected (0.01 sec)

mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

 

server2:
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)