MHA

MHA 集群

tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar 解压后安装

yum install  
     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

启动数据库更改密码

/etc/init.d/mysqld start    启动

grep "temporary password" /var/log/mysqld.log   在日志中差看初始密码

@localhost: qkyTPmwu#4lX

mysql_secure_installation     直接修改

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
ySuccess.    不允许root用户远程登陆

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...        移除test数据库
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!

测试是否能进去

在slave 中安装mysql

主从同步  配置

vi /etc/my.cnf
server-id=1    服务器标识  

log-bin=mysql-bin    二进制日志

保存退出重启mysql

进入数据库添加

grant REPLICATION SLAVE on *.* to repl@'172.25.254.%' identified by 'Westos+007';

Flush privileges;

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003

         Position: 1004

在slave 中

更改/etc/my.cnf

server-id=2

进入数据库执行

mysql> show slave status\G
Empty set (0.00 sec)

mysql> change master to master_host='172.25.254.1',master_user='repl',master_password=Westos+007,master_log_file='mysql-bin.000003',master_log_pos=1004;
Query OK, 0 rows affected, 2 warnings (0.46 sec)

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

IO和sql yes 就就说明成功了

测试 在master 中创建一个westos 表 在slave 中可以看到

在slave中查看

gtid 主从复制

在master 中执行

vim /etc/my.cnf

gtid_mode=ON

enforce-gtid-consistency=true

进入mysql 中

mysql> create table westos.user_tb(
    -> username varchar(15) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (0.76 sec)

mysql> desc westos.user_tb
    -> ;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO   |     | NULL    |       |
| password | varchar(15) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

在slave 中执行

在/etc/my.cnf  中添加

gtid_mode=ON
enforce-gtid-consistency=true

mysql> use westos;
Database changed
mysql> show tables;
Empty set (0.00 sec)
因为没有开启slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

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

mysql> desc user_tb;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO   |     | NULL    |       |
| password | varchar(15) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
 

slave 并行复制    就是多个事物同时复制

在slave的mysql 配置文件中添加
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE

relay_log_recovery=ON

重启动 mysql

进入mysql 查看

总共为16个

use mysql     进入mysql

show tables;    查看表 表中多了优化项

单点多个slave；

在添加一个slave

在server2中

添加

mysql> grant REPLICATION SLAVE on *.* to repl@'172.25.254.%' identified by 'Westso+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 mysqldump -p westos > westos.sql

把这个文件发送给 server3

编辑 /etc/my.cnf
server-id=3
gtid_mode=ON
enforce-gtid-consistency=true

重启mysql

/etc/init.d/mysqld restart

在server3 中更改mysql 配置文件 添加

server-id=3
gtid_mode=ON
enforce-gtid-consistency=true

重启数据库

/etc/init.d/mysqld restart

mysqladmin -p create westos;

mysql -p westos < westos.sql

进入mysql

change master to master_host='172.25.254.2',master_user='repl',master_password='Westos+007',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.70 sec)
mysql> start slave;
Query OK, 0 rows affected (0.09 sec)

在 server1 的中添加一条信息 在server2 和server3 中查看

server1

server 2和 3

半同步

 

 读写分离

在server3中

tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/    -C指定路径
cd /usr/local/
mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@server03 bin]# file mysql-proxy
mysql-proxy: POSIX shell script text executable
[root@server03 bin]# ./mysql-proxy

在 /usr/local/mysql-proxy    中建立 conf 目录

[root@server03 mysql-proxy]# mkdir conf
[root@server03 mysql-proxy]# cd conf/
[root@server03 conf]# vi mysql-proxy.conf
[root@server03 conf]# vi mysql-proxy.conf
[root@server03 conf]# cd ..
[root@server03 mysql-proxy]# mkdir logs
[root@server03 mysql-proxy]# vi /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

修改lua 脚本 方便实验

min_idle_connections = 1,

 max_idle_connections = 2,    最大链接两次就转slave

chmod 0660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

/usr/local/mysql-proxy/bin/mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

在master 中授权

mysql>  grant all on *.* to root@'%' identified by 'Westos+007';
Query OK, 0 rows affected, 1 warning (0.05 sec)

测试

当server3中创建表 server1和2 都有

 

都具有读的权力

当master 关闭 server3 不能写数据了

数据库开启 关闭server2 在server3和server1中插入数据 插入成功

说明master 具有读写的权力 而 slave 只具备读权力

 

 

组复制

清理之前的mysql
 /etc/init.d/mysqld stop
 rm -fr  /var/lib/mysql/*
 /etc/init.d/mysqld start
grep "temporary password" /var/log/mysqld.log    过滤出原始密码
mysql> alter user root@localhost identified by 'Westos+007';    更改密码
Query OK, 0 rows affected (0.06 sec)
mysql> flush privileges;    刷新
Query OK, 0 rows affected (0.06 sec)

 reset master； reset slave；因为之前的实验会和这个冲突

更改配置 /etc/my.cnf

server-id=1
gtid_mode=ON
enforce-gtid-consistency=true
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa--aaaa-aaaa-1111-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.254.1:24901"
loose-group_replication_group_seeds= "172.25.254.1:24901,172.25.254.2:24901,172.25.254.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replacation_single_primary_mode=FALSE
loose-group_replacation_single_enforce_update_everywhere_checks=TRUE
loose-group_replication_ip_whitelist='172.25.254.0/24'

server2 中 同样添加 修改 server-id=2 和 loose-group_replication_local_address="172.25.254.2:24901"

进入mysql 执行

mysql> SET SQL_LOG_BIN=0;

mysql> alter user root@localhost identified by 'Westos+007';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Westos+007' FOR CHANNEL 'group_replication_recovery';

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';

mysql> SHOW PLUGINS;

mysql> SET GLOBAL group_replication_bootstrap_group=ON;

mysql> SET GLOBAL group_replication_ip_whitelist='172.25.254.0/24';

mysql> START GROUP_REPLICATION;

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> SELECT * FROM performance_schema.replication_group_members;

 

在server2 中添加

mysql> SET SQL_LOG_BIN=0;

mysql> alter user root@localhost identified by 'Westos+007';

mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';

mysql> flush privileges;

mysql> set sql_log_bin=1;

mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';

mysql> install plugin group_replication soname 'group_replication.so';

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;

mysql> start group_replication;

mysql> select * from performance_schema.replication_group_members;

在server1中建立westos表

server2中查看同步了

数据库慢查询

 

mysql> FLUSH PRIVILEGES;mysql>
mysql> show variables like 'slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |

| slow_query_log      | OFF                          |        关闭状态

| slow_query_log_file | /var/lib/mysql/server02-slow.log |

 

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

mysql> show variables like 'slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | ON                               |

mysql> select sleep(10);    等待10秒执行
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)
mysql> show status like 'slow%';    显示
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 1     |
+---------------------+-------+
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (4.99 sec)

mysql> show status like 'slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 1     |
+---------------------+-------+

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

mysql> show status like 'slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 2     |

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

显示慢查询地址

mysql> show variables like '%slow%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| log_slow_admin_statements | OFF                              |
| log_slow_slave_statements | OFF                              |
| slow_launch_time          | 2                                |
| slow_query_log            | ON                               |
| slow_query_log_file       | /var/lib/mysql/server02-slow.log |

 

日志分析工具    mysqldumpslow

 

-s 表示按何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；

-t 表示top的意思，后面跟着的数据表示返回前面多少条；

-g 后面可以写正则表达式匹配，大小写不敏感。

 

 

 

 

 

日志分析工具    mysqldumpslow