Mysql (安装、配置、MHA教程等)

随笔2个月前发布 朕臻
34 0 0

一、1  mysql概述

MySQL是一个流行的开源关系型数据库管理系统,以其高性能、可靠性、易用性和跨平台支持而受到广泛欢迎。它使用SQL作为查询语言,支持多用户并发访问,适用于多种操作系统。MySQL广泛应用于Web和企业应用程序,能够处理大量数据和高并发请求,是许多大型网站和应用(如Facebook和Twitter)的首选数据库管理系统。它的优势包括开源免费、高可靠性和易于安装配置,支持多种编程语言,使其成为开发者的常用工具。

       2  基础命令的使用

默认端口号:3306
查看服务器版本:select version(); 或者 cmd命令 mysql -verison
登录数据库:mysql -uroot -p
退出数据库:exit/quit
查看当前系统下的数据库:show databases;
创建数据库:create 库名;
使用数据库:use 库名;
查看表:show tables;
建表:create table 表名 (字段名+空格+数据类型);
查看表结构:desc 表名;
添值:insert into 表名 (列名) values (值);
查看表中所有数据:select * from 表名;
查询建表时的结构:show create table 表名;
删除字段中的值:delete from 表名 where 条件;
删除表中的字段:delete from 表名 drop column 字段名;或者alter table 表名 drop 字段名
删除表:drop table 表名;
删除库:drop database 库名;
主键约束:primary key
唯一约束:unique
非空约束:not null
默认约束:default
外键约束:foreign key(外键)references主表(主键)
查看别的数据库的表格:show tables from 表名
 

二、源码编译

Mysql (安装、配置、MHA教程等)

三、mysql 的多种使用方法⭐⭐⭐

mysql的组从复制

Mysql (安装、配置、MHA教程等)

Mysql (安装、配置、MHA教程等)

配置salve4

Mysql (安装、配置、MHA教程等)

Mysql (安装、配置、MHA教程等)

延迟复制

Mysql (安装、配置、MHA教程等)

慢查询日志

Mysql (安装、配置、MHA教程等)

mysql的并行复制

Mysql (安装、配置、MHA教程等)

gtid模式

Mysql (安装、配置、MHA教程等)

Mysql (安装、配置、MHA教程等)

Mysql (安装、配置、MHA教程等)




[root@mysql-node1 ~]# ll


total 58416


-rw-------.  1 root root      1704 Jul 30 23:56 anaconda-ks.cfg


-rw-r--r--.  1 root root      1780 Jul 30 23:57 initial-setup-ks.cfg


-rw-r--r--   1 root root     93208 Aug 21 21:36 libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm


drwxr-xr-x  36 7161 31415     4096 Aug 21 22:23 mysql-5.7.44


-rw-r--r--   1 root root  53298645 Aug 21 21:33 mysql-boost-5.7.44.tar.gz


-rw-r--r--   1 root root   6400028 Aug 24 11:44 mysql-router-community-8.4.0-1.el7.x86_64.rpm


-rw-r--r--   1 root root        13 Aug 22 10:45 passwd


-rw-r--r--   1 root root      1945 Aug 22 15:25 test.sql


[root@mysql-node1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm 


 


[root@mysql-node1 ~]# vim /etc/my.cnf


[root@mysql-node1 ~]# cat /etc/my.cnf


[mysqld]


datadir=/data/mysql


socket=/data/mysql/mysql.sock


symbolic-links=0


server-id=10


log-bin=mysql-bin


gtid_mode=ON


enforce-gtid-consistency=ON


 


[root@mysql-node1 ~]# /etc/init.d/mysqld stop


 


[root@mysql-node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf


[root@mysql-node1 ~]# tail -5 /etc/mysqlrouter/mysqlrouter.conf


[routing:ro]


bind_address = 0.0.0.0


bind_port = 7001


destinations = 172.25.254.20:3306,172.25.254.30:3306


routing_strategy = round-robin


 


[root@mysql-node1 ~]# systemctl start mysqlrouter.service




[root@mysql-node2 ~]# vim /etc/my.cnf


[root@mysql-node2 ~]# cat /etc/my.cnf


[mysqld]


datadir=/data/mysql


socket=/data/mysql/mysql.sock


symbolic-links=0


server-id=20


gtid_mode=ON


enforce-gtid-consistency=ON


 


[root@mysql-node2 ~]# /etc/init.d/mysqld start


Starting MySQL. SUCCESS! 


[root@mysql-node2 ~]# mysql -uroot -p


 


#修改密码


mysql> alter user root@localhost identified by 'lee';


 


#添加远程登录用户


mysql> create user root@'%' identified by 'lee';


 


mysql> grant all ON *.* to root@'%';




root@mysql-node3 ~]# vim /etc/my.cnf


[root@mysql-node3 ~]# cat /etc/my.cnf


[mysqld]


datadir=/data/mysql


socket=/data/mysql/mysql.sock


symbolic-links=0


server-id=30


gtid_mode=ON


enforce-gtid-consistency=ON


 


[root@mysql-node3 ~]# /etc/init.d/mysqld start


Starting MySQL. SUCCESS! 


[root@mysql-node3 ~]# mysql -uroot -p


 


mysql> alter user root@localhost identified by 'lee';


 


mysql> create user root@'%' identified by 'lee';


 


mysql> grant all on *.* to root@'%';

实现mysql的复制




[root@mysql-node1 ~]# /etc/init.d/mysqld stop


[root@mysql-node1 ~]# ps aux | grep mysql


avahi       860  0.0  0.1  62272  2076 ?        Ss   13:28   0:00 avahi-daemon: runn[mysql-node1.local]


mysqlro+   2637  0.0  0.5 520980 10456 ?        Ssl  13:35   0:00 /usr/bin/mysqlrout


root       4004  0.0  0.0 112812   968 pts/0    S+   14:47   0:00 grep --color=auto l


[root@mysql-node1 ~]# kill -9 860


[root@mysql-node1 ~]# ps aux | grep mysql


mysqlro+   2637  0.0  0.5 520980 10456 ?        Ssl  13:35   0:00 /usr/bin/mysqlrout


root       4007  0.0  0.0 112812   968 pts/0    S+   14:47   0:00 grep --color=auto l


 


[root@mysql-node1 ~]# rm -rf /data/mysql/*


 


[root@mysql-node1 ~]# vim /etc/my.cnf


#初始化


[root@mysql-node1 ~]# mysqld --user=mysql initialize


#启动mysql


[root@mysql-node1 ~]# /etc/init.d/mysqld start


 


[root@mysql-node1 ~]# mysql -uroot -p'PsLOtfCdw8-x'


 


mysql> alter user root@localhost identified by 'lee';


Query OK, 0 rows affected (0.00 sec)


 


mysql> SET SQL_LOG_BIN=0;


Query OK, 0 rows affected (0.00 sec)


 


mysql> CREATE USER repl@'%' IDENTIFIED BY 'lee';


Query OK, 0 rows affected (0.00 sec)


 


mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';


Query OK, 0 rows affected (0.00 sec)


 


mysql> FLUSH PRIVILEGES;


Query OK, 0 rows affected (0.00 sec)


 


mysql> SET SQL_LOG_BIN=1;


Query OK, 0 rows affected (0.00 sec)


 


mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lee' FOR CHANNEL


    -> 'group_replication_recovery';


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


 


mysql> SET GLOBAL group_replication_bootstrap_group=ON;


Query OK, 0 rows affected (0.00 sec)


 


#开启组复制功能


mysql> START GROUP_REPLICATION;


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


 


mysql> SET GLOBAL group_replication_bootstrap_group=OFF;


Query OK, 0 rows affected (0.00 sec)


 


mysql> SELECT * FROM performance_schema.replication_group_members;


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


| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                 | MEMBER_PORT | MEMBER_STATE |


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


| group_replication_applier | db334b54-61e5-11ef-8ebd-000c291e0ede | mysql-node1.silingchuan.org |        3306 | ONLINE       |


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


1 row in set (0.00 sec)


 


mysql> SELECT * FROM performance_schema.replication_group_members;


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


| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOS


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


| group_replication_applier | 1b853fd1-61e7-11ef-876d-000c29e45e85 | mysql-node


| group_replication_applier | db334b54-61e5-11ef-8ebd-000c291e0ede | mysql-node


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


2 rows in set (0.00 sec)


 


mysql> SELECT * FROM performance_schema.replication_group_members;


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


| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOS


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


| group_replication_applier | 1b853fd1-61e7-11ef-876d-000c29e45e85 | mysql-node


| group_replication_applier | 8f504d00-61e7-11ef-902b-000c298b8db6 | mysql-node


| group_replication_applier | db334b54-61e5-11ef-8ebd-000c291e0ede | mysql-node


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


3 rows in set (0.00 sec)


 


 


mysql> quit


 


 


#地址解析


[root@mysql-node1 ~]# vim /etc/hosts


[root@mysql-node1 ~]# cat /etc/hosts


127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4


::1         localhost localhost.localdomain localhost6 localhost6.localdomain6


172.25.254.10	mysql-node1.silingchuan.org


172.25.254.20	mysql-node2.silingchuan.org


172.25.254.30	mysql-node3.silingchuan.org


 


 


[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf


 


[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf




[root@mysql-node1 ~]# cat /etc/my.cnf


[mysqld]


datadir=/data/mysql


socket=/data/mysql/mysql.sock


symbolic-links=0


server-id=10


gtid_mode=ON


enforce-gtid-consistency=ON


disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"


master_info_repository=TABLE


relay_log_info_repository=TABLE


binlog_checksum=NONE


log_slave_updates=ON


log_bin=binlog


binlog_format=ROW


plugin_load_add='group_replication.so'


transaction_write_set_extraction=XXHASH64


group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"


group_replication_start_on_boot=off


group_replication_local_address="172.25.254.10:33061"


group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"


group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"


group_replication_bootstrap_group=off


group_replication_single_primary_mode=OFF


group_replication_enforce_update_everywhere_checks=ON


group_replication_allow_local_disjoint_gtids_join=1




[root@mysql-node2 ~]# /etc/init.d/mysqld stop


 


[root@mysql-node2 ~]# ps aux | grep mysql


avahi       794  0.0  0.1  62272  2072 ?        Ss   13:28   0:00 avahi-daemon:[mysql-node2.local]


root       3601  0.0  0.0 112812   964 pts/0    S+   14:47   0:00 grep --color=l


[root@mysql-node2 ~]# kill -9 794


[root@mysql-node2 ~]# ps aux | grep mysql


root       3604  0.0  0.0 112808   964 pts/0    S+   14:47   0:00 grep --color=l


 


[root@mysql-node2 ~]# rm -rf /data/mysql/*


[root@mysql-node2 ~]# vim /etc/hosts		#三台主机解析一致


 


[root@mysql-node2 ~]# ls /data/mysql/


[root@mysql-node2 ~]# vim /etc/my.cnf


 


[root@mysql-node2 ~]# mysqld --user=mysql --initialize


 


[root@mysql-node3 ~]# /etc/init.d/mysqld start


 


[root@mysql-node2 ~]# mysql -uroot -p'!lsogVo)m0KS'


 


 


mysql> alter user root@localhost identified by 'lee';


Query OK, 0 rows affected (0.00 sec)


 


mysql> SET SQL_LOG_BIN=0;


Query OK, 0 rows affected (0.00 sec)


 


mysql> CREATE USER repl@'%' IDENTIFIED BY 'lee';


Query OK, 0 rows affected (0.00 sec)


 


mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';


Query OK, 0 rows affected (0.00 sec)


 


mysql> FLUSH PRIVILEGES;


Query OK, 0 rows affected (0.00 sec)


 


mysql> SET SQL_LOG_BIN=1;


Query OK, 0 rows affected (0.00 sec)


 


mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lee' FOR CHANNEL


    -> 'group_replication_recovery';


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


 


mysql> START GROUP_REPLICATION;


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




[root@mysql-node2 ~]# cat /etc/my.cnf


[mysqld]


datadir=/data/mysql


socket=/data/mysql/mysql.sock


symbolic-links=0


server-id=20


gtid_mode=ON


enforce-gtid-consistency=ON


disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"


master_info_repository=TABLE


relay_log_info_repository=TABLE


binlog_checksum=NONE


log_slave_updates=ON


log_bin=binlog


binlog_format=ROW


plugin_load_add='group_replication.so'


transaction_write_set_extraction=XXHASH64


group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"


group_replication_start_on_boot=off


group_replication_local_address="172.25.254.20:33061"


group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"


group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"


group_replication_bootstrap_group=off


group_replication_single_primary_mode=OFF


group_replication_enforce_update_everywhere_checks=ON


group_replication_allow_local_disjoint_gtids_join=1

运行结果




mysql> #20


mysql> DESC test.userlist;


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


| Field    | Type        | Null | Key | Default | Extra |


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


| username | varchar(10) | NO   | PRI | NULL    |       |


| password | varchar(40) | NO   |     | NULL    |       |


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


2 rows in set (0.01 sec)


 


mysql> INSERT INTO test.userlist VALUES ('user1','111');


Query OK, 1 row affected (0.00 sec)



#10


mysql> SELECT * FROM test.userlist;


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


| username | password |


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


| user1    | 111      |


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


1 row in set (0.00 sec)

高可用MHA

Mysql (安装、配置、MHA教程等)




[root@mysql-node1 ~]# vim /etc/my.cnf


[mysqld]


datadir=/data/mysql


socket=/data/mysql/mysql.sock


symbolic-links=0


log-bin=mysql-bin


server-id=10


gtid_mode=ON


enforce-gtid-consistency=ON


 


 


[root@mysql-node1 ~]# mysql -uroot -p'itPOG(_)2:;K'


mysql> alter user root@localhost identified by 'redhat';


Query OK, 0 rows affected (0.00 sec)


 


###这条命令是在数据库中创建一个新用户的 SQL 语句,其具体含义如下:“CREATE USER”:表示创建用户的 SQL 关键字。


###“'repl'@'%'”:这里 “repl” 是用户名,“@'%'” 表示这个用户可以从任何 IP 地址连接到数据库服务器。其中 “%” 是通配符,表示任意 IP 地址。


###“IDENTIFIED BY 'ren'”:表示设置这个用户的密码为 “ren”。


mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'ren';


Query OK, 0 rows affected (0.00 sec)


 


####授予用户 “repl” 从任何 IP 地址('%')连接到数据库服务器并拥有复制从服务器权限(REPLICATION SLAVE),且这个权限作用于所有数据库(.)


####“GRANT”:是 SQL 中的授权关键字。


####“REPLICATION SLAVE”:表示复制从服务器的权限,拥有这个权限的用户可以连接到主数据库服务器并从主服务器接收复制数据,从而实现数据库的复制功能。


####“ON .”:表示这个权限作用于所有的数据库和所有的数据库对象。


####“TO repl@'%'”:指定将这个权限授予用户 “repl”,该用户可以从任何 IP 地址连接到数据库服务器。


mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';


Query OK, 0 rows affected (0.00 sec)


 


###安装“rpl_semi_sync_master”的插件,插件的共享库文件名soname


###指定该插件的共享库文件名为"semisync_master.so"


###insert plugin 用于在数据库中安装插件的关键字


###“rpl_semi_sync_master”:插件名称,通常与 MySQL 的半同步复制主服务器功能相关。


mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';


Query OK, 0 rows affected (0.00 sec)


 


###将全局变量“rpl_semi_sync_master_enabled”设为1


###SET GLOBAL  表示设置一个全局的数据库服务器变量


###“rpl_semi_sync_master_enabled”:这是与 MySQL 半同步复制主服务器相关的变量。当这个变量设置为 1 时,表示启用半同步复制主服务器功能。如果设置为 0,则表示禁用该功能。


###启用半同步复制主服务器功能后,主数据库服务器在提交事务时会等待至少一个从服务器确认收到事务,从而提高数据的一致性和可靠性,但可能会对性能产生一定的影响。


mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;


Query OK, 0 rows affected (0.00 sec)


 


mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';


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


| 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.01 sec)


 


mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';


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


| 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             | 0     |


| Rpl_semi_sync_master_no_times              | 0     |


| Rpl_semi_sync_master_no_tx                 | 0     |


| Rpl_semi_sync_master_status                | ON    |


| Rpl_semi_sync_master_timefunc_failures     | 0     |


| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |


| Rpl_semi_sync_master_tx_wait_time          | 0     |


| Rpl_semi_sync_master_tx_waits              | 0     |


| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |


| Rpl_semi_sync_master_wait_sessions         | 0     |


| Rpl_semi_sync_master_yes_tx                | 0     |


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


14 rows in set (0.00 sec)


 


mysql> quit


Bye

在 10 和 20 中设置




[root@mysql-node1 ~]# vim /etc/my.cnf


[mysqld]


datadir=/data/mysql


socket=/data/mysql/mysql.sock


symbolic-links=0


log-bin=mysql-bin


server-id=20   和30


gtid_mode=ON


enforce-gtid-consistency=ON


 


####同样还原数据库,重新初始化数据库


[root@mysql-node2 ~]# mysqld --user=mysql --initialize


[root@mysql-node2 ~]# /etc/init.d/mysqld start


[root@mysql-node2 ~]# mysql -uroot -p'/FWosRqiv2Vg'


 


mysql> alter user root@localhost identified by 'redhat';


Query OK, 0 rows affected (0.00 sec)


 


mysql> change master to master_host='172.25.250.10',master_user='repl',master_password='ren',master_auto_position=1;


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


 


mysql> start slave;


Query OK, 0 rows affected (0.00 sec)


 


mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';


Query OK, 0 rows affected (0.00 sec)


 


mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;


Query OK, 0 rows affected (0.00 sec)


 


mysql> stop slave io_thread;


Query OK, 0 rows affected (0.00 sec)


 


mysql> start slave io_thread;


Query OK, 0 rows affected (0.00 sec)


 


mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';


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


| 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.01 sec)


 


mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';


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


| 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             | 0     |


| Rpl_semi_sync_master_no_times              | 0     |


| Rpl_semi_sync_master_no_tx                 | 0     |


| Rpl_semi_sync_master_status                | ON    |


| Rpl_semi_sync_master_timefunc_failures     | 0     |


| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |


| Rpl_semi_sync_master_tx_wait_time          | 0     |


| Rpl_semi_sync_master_tx_waits              | 0     |


| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |


| Rpl_semi_sync_master_wait_sessions         | 0     |


| Rpl_semi_sync_master_yes_tx                | 0     |


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


14 rows in set (0.00 sec)

安装 MHA所需的文件




[root@mysql-mha ~]# ls


anaconda-ks.cfg  initial-setup-ks.cfg  MHA-7.zip


[root@mysql-mha ~]# rz -E


rz waiting to receive.


[root@mysql-mha ~]# rz -E


rz waiting to receive.


[root@mysql-mha ~]# unzip MHA-7.zip 


Archive:  MHA-7.zip


   creating: MHA-7/


  inflating: MHA-7/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  


  inflating: MHA-7/mha4mysql-manager-0.58.tar.gz  


  inflating: MHA-7/mha4mysql-node-0.58-0.el7.centos.noarch.rpm  


  inflating: MHA-7/perl-Config-Tiny-2.14-7.el7.noarch.rpm  


  inflating: MHA-7/perl-Email-Date-Format-1.002-15.el7.noarch.rpm  


  inflating: MHA-7/perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm  


  inflating: MHA-7/perl-Mail-Sender-0.8.23-1.el7.noarch.rpm  


  inflating: MHA-7/perl-Mail-Sendmail-0.79-21.el7.noarch.rpm  


  inflating: MHA-7/perl-MIME-Lite-3.030-1.el7.noarch.rpm  


  inflating: MHA-7/perl-MIME-Types-1.38-2.el7.noarch.rpm  


  inflating: MHA-7/perl-Net-Telnet-3.03-19.el7.noarch.rpm  


  inflating: MHA-7/perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm  


[root@mysql-mha ~]# ls


anaconda-ks.cfg       master_ip_failover       MHA-7


initial-setup-ks.cfg  master_ip_online_change  MHA-7.zip


[root@mysql-mha ~]# cd MHA-7/


[root@mysql-mha MHA-7]# ls


mha4mysql-manager-0.58-0.el7.centos.noarch.rpm


mha4mysql-manager-0.58.tar.gz


mha4mysql-node-0.58-0.el7.centos.noarch.rpm


perl-Config-Tiny-2.14-7.el7.noarch.rpm


perl-Email-Date-Format-1.002-15.el7.noarch.rpm


perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm


perl-Mail-Sender-0.8.23-1.el7.noarch.rpm


perl-Mail-Sendmail-0.79-21.el7.noarch.rpm


perl-MIME-Lite-3.030-1.el7.noarch.rpm


perl-MIME-Types-1.38-2.el7.noarch.rpm


perl-Net-Telnet-3.03-19.el7.noarch.rpm


perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm


[root@mysql-mha MHA-7]# ssh-keygen 


 


[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.250.10


[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.250.20


[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.250.20


 


[root@mysql-mha MHA-7]# vim /etc/hosts


127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4


::1         localhost localhost.localdomain localhost6 localhost6.localdomain6


172.25.250.40   mysql-mha


172.25.250.20   mysql-node2


172.25.250.10   mysql-node1


172.25.250.30   mysql-node3


 


 


[root@mysql-mha MHA-7]# ls


mha4mysql-manager-0.58-0.el7.centos.noarch.rpm


mha4mysql-manager-0.58.tar.gz


mha4mysql-node-0.58-0.el7.centos.noarch.rpm


perl-Config-Tiny-2.14-7.el7.noarch.rpm


perl-Email-Date-Format-1.002-15.el7.noarch.rpm


perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm


perl-Mail-Sender-0.8.23-1.el7.noarch.rpm


perl-Mail-Sendmail-0.79-21.el7.noarch.rpm


perl-MIME-Lite-3.030-1.el7.noarch.rpm


perl-MIME-Types-1.38-2.el7.noarch.rpm


perl-Net-Telnet-3.03-19.el7.noarch.rpm


perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm


[root@mysql-mha MHA-7]# yum install *.rpm -y


 


 


[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.250.10:/root


mha4mysql-node-0.58-0.el7.centos.noarch.rpm               100%   35KB  24.6MB/s   00:00    


[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.250.20:/root


mha4mysql-node-0.58-0.el7.centos.noarch.rpm               100%   35KB  29.6MB/s   00:00    


[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.250.30:/root


mha4mysql-node-0.58-0.el7.centos.noarch.rpm               100%   35KB  19.7MB/s   00:00    


[root@mysql-mha MHA-7]# masterha_


masterha_check_repl       masterha_conf_host        masterha_master_switch


masterha_check_ssh        masterha_manager          masterha_secondary_check


masterha_check_status     masterha_master_monitor   masterha_stop


[root@mysql-mha MHA-7]# cd


[root@mysql-mha ~]# masterha_manager --help


Usage:


    masterha_manager --global_conf=/etc/masterha_default.cnf


    --conf=/usr/local/masterha/conf/app1.cnf


 


    See online reference


    (http://code.google.com/p/mysql-master-ha/wiki/masterha_manager) for


    details.


 


[root@mysql-mha ~]# mkdir /etc/masterha


[root@mysql-mha ~]# cd MHA-7/


 


[root@mysql-mha MHA-7]# ls


mha4mysql-manager-0.58-0.el7.centos.noarch.rpm


mha4mysql-manager-0.58.tar.gz


mha4mysql-node-0.58-0.el7.centos.noarch.rpm


perl-Config-Tiny-2.14-7.el7.noarch.rpm


perl-Email-Date-Format-1.002-15.el7.noarch.rpm


perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm


perl-Mail-Sender-0.8.23-1.el7.noarch.rpm


perl-Mail-Sendmail-0.79-21.el7.noarch.rpm


perl-MIME-Lite-3.030-1.el7.noarch.rpm


perl-MIME-Types-1.38-2.el7.noarch.rpm


perl-Net-Telnet-3.03-19.el7.noarch.rpm


perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm


[root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz 


[root@mysql-mha MHA-7]# cd mha4mysql-manager-0.58/


[root@mysql-mha mha4mysql-manager-0.58]# ls


AUTHORS  COPYING  lib          MANIFEST       README  samples  tests


bin      debian   Makefile.PL  MANIFEST.SKIP  rpm     t


[root@mysql-mha mha4mysql-manager-0.58]# cd samples/conf/


[root@mysql-mha conf]# ls


app1.cnf  masterha_default.cnf


[root@mysql-mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf


[root@mysql-mha conf]# cd /etc/masterha/


[root@mysql-mha masterha]# ls


app1.cnf


 


 


[root@mysql-mha masterha]# vim app1.cnf 


[server default]


user=root


password=ren


ssh_user=root


master_binlog_dir= /data/mysql


remote_workdir=/tmp


secondary_check_script= masterha_secondary_check -s 172.25.250.10 -s 172.25.250.11


ping_interval=3


# master_ip_failover_script= /script/masterha/master_ip_failover


# shutdown_script= /script/masterha/power_manager


# report_script= /script/masterha/send_report


# master_ip_online_change_script= /script/masterha/master_ip_online_change


[server default]


manager_workdir=/etc/masterha


manager_log=/etc/masterha/manager.log


 


[server1]


hostname=172.25.250.10


candidate_master=1


check_repl_delay=0


 


[server2]


hostname=172.25.250.20


candidate_master=1


check_repl_delay=0


 


[server3]


hostname=172.25.250.30


no_master=1


 


[root@mysql-mha ~]# scp id_rsa root@172.25.250.10:/root/.ssh/


[root@mysql-mha ~]# scp id_rsa root@172.25.250.20:/root/.ssh/


[root@mysql-mha ~]# scp id_rsa root@172.25.250.30:/root/.ssh/


 


 


[root@mysql-mha ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf


 


[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

MHA的故障切换

手动切换




[root@mysql-mha ~]# masterha_master_switch 


--conf=/etc/masterha/app1.cnf  		#指定配置文件


--master_state=alive  					#指定master节点状态


--new_master_host=172.25.254.20 		 #指定新master节点


--new_master_port=3306  					#执行新master节点端口


--orig_master_is_new_slave  			#原始master会变成新的slave


--running_updates_limit=10000 			#切换的超时时间


 


####上面是原master是172.25.250.10,新master是172.25.250.20


####如果想要切换回来的话


[root@mysql-mha ~]# masterha_master_switch 


--conf=/etc/masterha/app1.cnf  		#指定配置文件


--master_state=alive  					#指定master节点状态


--new_master_host=172.25.254.10 		 #指定新master节点


--new_master_port=3306  					#执行新master节点端口


--orig_master_is_new_slave  			#原始master会变成新的slave


--running_updates_limit=10000 			#切换的超时时间

自动切换




[root@mysql-mha masterha]# rm -fr app1.failover.complete #删掉切换锁文件


 


#监控程序通过指定配置文件监控master状态,当master出问题后自动切换并退出避免重复做故障切换


[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf 


[root@mysql-mha masterha]# cat /etc/masterha/manager.log

模拟故障测试




[root@mysql-node1 mysql]# /etc/init.d/mysqld stop


 


###停掉之后我们监控和看日志


[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf 


[root@mysql-mha masterha]# cat /etc/masterha/manager.log


 


###切换完成新master为172.25.250.20

然后恢复节点后 再测试




[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf


 


###[root@mysql-node1 mysql]# /etc/init.d/mysqld start


 


 


[root@mysql-node1 mysql]# mysql -u -predhat


mysql> slave stop


mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_USER='repl', MASTER_PASSWORD='ren',MASTER_AUTO_POSITION=1


mysql> slave start


mysql> show slave statusG;


 


###检测


[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf


 


###需要注意的时,自动切换完后都会生成一个锁文件,如果再次启动自动切换的监控,需要把它清除掉


[root@mysql-mha masterha]# rm -rf app1.failover.complete manager.log

添加VIP功能




[root@mysql-mha ~]# ls


anaconda-ks.cfg       master_ip_failover       MHA-7


initial-setup-ks.cfg  master_ip_online_change  MHA-7.zip


[root@mysql-mha ~]# file master_ip_failover 


master_ip_failover: Perl script, ASCII text executable


[root@mysql-mha ~]# cp master_ip_* /usr/local/bin/master_ip_*


[root@mysql-mha ~]# chmod +x /usr/local/bin/master_ip_*


 


####修改脚本在脚本中只需要修改下vip


[root@mysql-mha ~]# vim /usr/local/bin/master_ip_failover


my $vip = '172.25.250.100/24';


my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";


my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";


 


[root@mysql-mha ~]# vim /usr/local/bin/master_ip_online_change


my $vip = '172.25.250.100/24';


my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";


my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";


my $exit_code = 0;


 


[root@mysql-mha ~]# vim /etc/masterha/app1.cnf 


[server default]


user=root


password=ren


ssh_user=root


master_binlog_dir= /data/mysql


remote_workdir=/tmp


secondary_check_script= masterha_secondary_check -s 172.25.250.10 -s 172.25.250.11


ping_interval=3


 master_ip_failover_script= /usr/local/master_ip_failover


# shutdown_script= /script/masterha/power_manager


# report_script= /script/masterha/send_report


 master_ip_online_change_script= /usr/local/bin/master_ip_online_change


[server default]


manager_workdir=/etc/masterha


manager_log=/etc/masterha/manager.log


 


[server1]


hostname=172.25.250.10


candidate_master=1


check_repl_delay=0


 


[server2]


hostname=172.25.250.20


candidate_master=1


check_repl_delay=0


 


[server3]


hostname=172.25.250.30


no_master=1

Mysql (安装、配置、MHA教程等)Mysql (安装、配置、MHA教程等)




ip a a 172.25.250.100/24 dev eth0


 


###然后我们模拟master故障,关闭master的mysql


/etc/init.d/mysqld stop 


 


##我们可以观察到VIP由原来的172.25.250.10(原master)转到172.25.250.20(新master)


 


###我们恢复故障机


/etc/init.d/mysqld start


mysql -uroot -predhat


 


mysql> stop slave;


mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_USER='repl', MASTER_PASSWORD='ren',MASTER_AUTO_POSITION=1


mysql> start slave;


mysql> show slave statusG;

Mysql (安装、配置、MHA教程等)




[root@mysql-mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --


orig_master_is_new_slave --running_updates_limit=10000


 


###旧master172.25.250.20 新master172.25.250.10


VIP转到172.25.250.10上

四、总结

MySQL是一种流行的开源关系型数据库管理系统,以其高性能、可靠性、易用性、跨平台特性和免费开源的优势,在全球范围内被广泛使用。它支持多用户并发访问,使用SQL作为查询语言,适用于多种操作系统,并支持多种编程语言,使其成为开发Web和企业应用程序的理想选择。MySQL在处理大量数据和高并发请求方面表现出色,被众多知名网站和应用(如Facebook、Twitter)作为数据库管理系统使用。

© 版权声明

相关文章

暂无评论

您必须登录才能参与评论!
立即登录
暂无评论...