一、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 的多种使用方法⭐⭐⭐
mysql的组从复制
配置salve4
延迟复制
慢查询日志
mysql的并行复制
gtid模式
[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
[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
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;
[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）作为数据库管理系统使用。
© 版权声明
特别提醒: 内容为用户自行发布,如有侵权,请联系我们管理员删除,邮箱:mail@xieniao.com ,在收到您的邮件后我们会在3个工作日内处理。
相关文章
暂无评论...
XieNiao 血鸟导航，集网址、资源、资讯于一体的定制化导航主题，简约优雅的设计风格，自定义网址的用户功能，自定义主页，欢迎您的体验
赣公网安备36020002000448号