mysql5.7主从复制 半同步.....

一:mysql5.7主从复制 (主从复制为‘异步‘,可能会导致数据丢失)

[root@server8 ~]为主库
[root@server9 ~]为从库

[root@server8 ~]上:
1 mysql安装包
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm

2 安装这5个mysql包
[root@server8 ~]# yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm

3 初始化mysql数据库
[root@server8 ~]# /etc/init.d/mysqld start
初始化 MySQL 数据库: [确定]
Installing validate password plugin: [确定]
正在启动 mysqld: [确定]

4 查询初始化密码( -Hu#pa;oY8x8)
[root@server8 ~]# grep password /var/log/mysqld.log
2018-07-05T01:53:53.506087Z 1 [Note] A temporary password is generated for root@localhost: -Hu#pa;oY8x8
2018-07-05T01:54:55.290750Z 0 [Note] Execution of init_file ‘/var/lib/mysql/install-validate-password-plugin.ErrKj7.sql’ started.

5 修改初始化密码
[root@server8 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:
The ‘validate_password’ plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

… skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database…
Success.

  • Removing privileges on test database…
    Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
6 进入数据库
[root@server8 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17 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>

7 修改配置文件(主库)
[root@server8 ~]# vim /etc/my.cnf
这里写图片描述

8 重起mysql服务,进入mysql
[root@server8 ~]# /etc/init.d/mysqld restart
停止 mysqld: [确定]
正在启动 mysqld: [确定]
[root@server8 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log 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>

9 查看master状态
mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 154 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

10 授权此网段在所有库中拥有所有权限,并授予密码。
mysql> grant replication slave on . to repl@’172.25.16.%’ identified by’liYI1988618@’;
Query OK, 0 rows affected, 1 warning (0.66 sec)

11 查看master状态
mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 447 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

12 查看所有资料库内容
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.00 sec)

13 新建westos数据库
mysql> create databases westos;

14 进入westos这个库创建表格
mysql> use westos
Database changed
mysql> create table usertb(
-> username varchar(20) not null, 行名字(username) 字符数(20)不能为空
-> password varchar(25) not null); 行名字(password) 字符数(25)不能为空

15 查看表格结构
mysql> desc usertb;
+———-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| username | varchar(20) | NO | | NULL | |
| password | varchar(25) | NO | | NULL | |
+———-+————-+——+—–+———+——-+
2 rows in set (0.01 sec)

16 插入数据到建好的表格里
mysql> insert into usertb values (‘user1’,’123’);
Query OK, 1 row affected (0.31 sec)

mysql> insert into usertb values (‘user2’,’456’);
Query OK, 1 row affected (0.25 sec)

mysql> insert into usertb values (‘user3’,’789’);
Query OK, 1 row affected (0.12 sec)

[root@server9 ~]上:
1 mysql安装包
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm

2 安装这5个mysql包
[root@server9 ~]# yum install -y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm

3 初始化mysql数据库
[root@server9 ~]# /etc/init.d/mysqld start
初始化 MySQL 数据库: [确定]
Installing validate password plugin: [确定]
正在启动 mysqld: [确定]

4 查询初始化密码( -Hu#pa;oY8x8)
[root@server9 ~]# grep password /var/log/mysqld.log
2018-07-05T0
1:53:53.506087Z 1 [Note] A temporary password is generated for root@localhost: 54:03.978482Z
2018-07-05T01:54:55.290750Z 0 [Note] Execution of init_file ‘/var/lib/mysql/install-validate-password-plugin.ErrKj7.sql’ started.

5 修改初始化密码
[root@server9 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:
The ‘validate_password’ plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

… skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
‘localhost’. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named ‘test’ that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database…
Success.

  • Removing privileges on test database…
    Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
6 进入数据库
[root@server9 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17 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>

7 修改配置文件(从库)
[root@server9 ~]# vim /etc/my.cnf
这里写图片描述

8 重起mysql服务,进入mysql
[root@server8 ~]# /etc/init.d/mysqld restart
停止 mysqld: [确定]
正在启动 mysqld: [确定]
[root@server8 ~]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17-log 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>

9 连接主库
mysql> change master to master_host=’172.25.16.8’,master_user=’repl’,master_password=’liYI1988618@’,master_log_file=’mysql-bin.000001’, master_log_pos=740;
Query OK, 0 rows affected, 2 warnings (1.13 sec)

10 启动slave,查看从库slave状态。
mysql> start slave;
Query OK, 0 rows affected (0.55 sec)

mysql> show slave status\G;
***************** 1. row *****************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.16.8
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 740
Relay_Log_File: server9-relay-bin.000002
Relay_Log_Pos: 320
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: 740
Relay_Log_Space: 529
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: 1
Master_UUID: 452bddba-7ff6-11e8-b28f-525400484d0b
Master_Info_File: /var/lib/mysql/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_TLS_Version:
1 row in set (0.00 sec)
*注意:当看到Slave_IO_Running: Yes以及Slave_SQL_Running: Yes,则表示slave库已经正常运行了
当出现Slave_IO_Running: Connecting的提示时,说明主库和从库没有连接上,有以下三点原因:
1.网络问题:检查网络连接是否能够连接上
2.密码或POS号错误:查看pos号和主库的号是否对应
3.防火墙的问题:查看主库防火墙的策略,数据库是否拒绝外来连接,然后做相应的改动

11 查看数据库
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| westos |
+——————–+
5 rows in set (0.00 sec)

12 查看表格内容(此时同步已成功,从库显示这三个用户为主库新建,从库并未建立。)
mysql> select * from westos.usertb;
+———-+———-+
| username | password |
+———-+———-+
| user1 | 123 |
| user2 | 456 |
| user3 | 789 |
+———-+———-+
3 rows in set (0.00 sec)

二:mysql半同步
1 主库加载 master 模块并且开启:
mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so’;(加载模块)
Query OK, 0 rows affected (0.48 sec)

mysql> set global rpl_semi_sync_master_enabled=1;(开启)
Query OK, 0 rows affected (0.00 sec)

2 从库加载 master 模块并且开启:
mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;(加载模块)
Query OK, 0 rows affected (0.21 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;(开启)
Query OK, 0 rows affected (0.00 sec)

3 然后在主库端查看配置情况:
mysql> show variables like ‘%semi%’;
+——————————————-+————+
| Variable_name | Value |
+——————————————-+————+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+——————————————-+————+
6 rows in set (0.10 sec)

4 在从库端查看配置情况:
mysql> show variables like ‘%semi%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+———————————+——-+
2 rows in set (0.00 sec)

注意:rpl_semi_sync_master_enabled 控制 master 是否开启半同步;
rpl_semi_sync_master_timeout 控制 master 等待多久被告知 slave 已经收到;即超时时间;
rpl_semi_sync_slave_enabled 控制 master 是否开启半同步;

5 主库上查看Value
mysql> show status like ‘%semi%’;
+——————————————–+——-+
| Variable_name | Value |
+——————————————–+——-+
| Rpl_semi_sync_master_clients | 1 |(检测到一个与主库连接 的从库)
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 465 |
| Rpl_semi_sync_master_tx_wait_time | 465 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+——————————————–+——-+
14 rows in set (0.00 sec)
Rpl_semi_sync_master_clients:查看有多少个开启半同步复制的插件的 slave;此
时只有一个;
Rpl_semi_sync_master_status: 查看 master 上半同步复制是否正在运行;值为
ON,说明正在运行,并且已被告知有 slave 收到;为 OFF 时,说明 master 未启用半同
步,或者没有被告知;
Rpl_semi_sync_master_no_tx:查看有多少事务没有经过半同步复制的机制进行复
制;
Rpl_semi_sync_master_yes_tx:查看有多少事务经过半同步复制的机制进行复制;

6 从库:
mysql> show status like ‘%semi%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Rpl_semi_sync_slave_status | ON |
+—————————-+——-+
1 row in set (0.00 sec)

如果显示Rpl_semi_sync_slave_status 为OFF
执行 mysql> stop slave io_thread;此名令,关掉io模块

Rpl_semi_sync_slave_status: 查看 slave 上半同步复制是否正常运行,值为 ON
时,说明 slave 正通过半同步复制且 slave I/O 正在运行;为 OFF 时,反之。

7 实验:
在主从半同步开启的状态下,在主库上建立数据库,速度非常快,用了 0.12s
mysql> create database chaoren1;
Query OK, 1 row affected (0.12 sec)

关闭从服务:
mysql> stop slave;
Query OK, 0 rows affected (0.41 sec)

mysql> set global rpl_semi_sync_slave_enabled=0;
Query OK, 0 rows affected (0.00 sec)

主库再次创建数据库的时候会等待,当超时时间过了以后才再次创建(timoeout :
10000ms 即 10s)
mysql> create database chaoren2;
Query OK, 1 row affected (10.10 sec)

一旦超时,他就自动降级为异步,再次创建数据库的时候也不再等待
mysql> create database chaoren3;
Query OK, 1 row affected (0.16 sec)

此时查看主库 slave,已经不存在了:(第一行,value 值为 0,没有 slave)
mysql> show status like ‘%semi%’;
+——————————————–+——-+
| Variable_name | Value |
+——————————————–+——-+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 465 |
| Rpl_semi_sync_master_tx_wait_time | 465 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+——————————————–+——-+
14 rows in set (0.00 sec)

然后把从服务器上的复制进程开启,就会自动向主进程同步数据:
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| chaoren1 |
| chaoren2 |
| chaoren3 |
| mysql |
| performance_schema |
| sys |
| westos |
+——————–+
8 rows in set (0.00 sec)

主库查看 slave,又变成了 1:
mysql> show status like ‘%semi%’;
+——————————————–+——-+
| Variable_name | Value |
+——————————————–+——-+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 2 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 465 |
| Rpl_semi_sync_master_tx_wait_time | 465 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+——————————————–+——-+
14 rows in set (0.00 sec)

数据库复制的半同步方式的架构已经实现。

阅读更多
上一篇lvs健康检查,更新策略(ldirectord)
下一篇理解LNMP体系结构
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭