MySQL下载方法
下载路径
https://www.mysql.com/
MD5校验下载的软件包
[root@localhost ~]# md5sum mysql-5.7.38-1.el7.x86_64.rpm-bundle.tar
826ce05d0379574a03935b62ae02db88 mysql-5.7.38-1.el7.x86_64.rpm-bundle.tar
解压下载的软件包
[root@localhost ~]# tar -xvf mysql-5.7.38-1.el7.x86_64.rpm-bundle.tar
# 检查是否已有安装mariadb，如果已安装请卸载
[root@localhost ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@localhost ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
[root@localhost ~]# rpm -ivh mysql-community-common-5.7.38-1.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh mysql-community-libs-5.7.38-1.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh mysql-community-client-5.7.38-1.el7.x86_64.rpm
[root@localhost ~]# rpm -ivh mysql-community-server-5.7.38-1.el7.x86_64.rpm
# 如果community-server安装时报perl错误，请安装perl后继续安装community-server
启动MySQL服务
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
修改初始密码
[root@localhost ~]# grep password /var/log/mysqld.log
2022-09-27T03:41:05.509999Z 1 [Note] A temporary password is generated for root@localhost: wBjr:f);r8dp
[root@localhost ~]# mysql -uroot -p
Enter password: wBjr:f);r8dp
mysql> alter user 'root'@'localhost' identified by 'Root@123456'; ##MySQL默认密码需要8位以上大小写字母数字特殊符号都需要
Query OK, 0 rows affected, 1 warning (0.00 sec)
#可以按照以下方法修改密码策略
show variables like '%validate_password%' #查看密码策略
set global validate_password_policy=LOW; #修改密码策略等级为LOW
set global validate_password_length=4; #修改密码最小长度
set global validate_password_mixed_case_count=0; #设置密码中至少要包含0个大写字母和小写字母
set global validate_password_number_count=0; #设置密码中至少要包含0个数字
set global validate_password_special_char_count=0; #设置密码中至少要包含0个特殊字符
设置远程访问
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'Root@123456' WITH GRANT OPTION;
mysql> flush privileges;
mysql> use mysql;
mysql> select user,host from user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | % |
| mysql.sys | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)
修改字符集
# 未改动前字符集
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
[root@localhost ~]# vi /etc/my.cnf # 末尾追加
character-set-server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
# 重启验证
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -pRoot@123456
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
修改数据存储路径
[root@localhost ~]# mkdir /data/
[root@localhost ~]# mysqladmin -u root -p shutdown
Enter password: Root@123456
[root@localhost ~]# cp -R /var/lib/mysql /data/
[root@localhost ~]# chown -R mysql:mysql /data/mysql/
[root@localhost ~]# vi /etc/my.cnf
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
datadir=/data/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[root@localhost ~]# systemctl restart mysqld
问题：如果重启完成后，连接出现一下问题
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
解决：
这种情况大多数是因为mysql是使用rpm方式安装的，它会自动寻找 /var/lib/mysql/mysql.sock 这个文件，添加软连接
[root@localhost mysql]# ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock
MySQL主从复制基于5.6
此方式基于MySQL5.6
环境
| 操作系统 |
IP |
主机名 |
软件包 |
| centos7.4 |
192.168.100.107 |
master |
mysql-community-server |
| centos7.4 |
192.168.100.108 |
slave |
mysql-community-server |
安装MySQL
[root@master ~]# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
[root@master ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
[root@master ~]# yum -y install mysql-community-server
[root@master ~]# systemctl start mysqld --now
[root@master ~]# netstat -utpln |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1442/mysqld
[root@master ~]# mysql -uroot -p
Enter password: #两次回车进入
mysql>
修改密码
mysql> use mysql;
mysql> update user set password=password("123123") where user='root';
mysql> flush privileges;
mysql> exit;
[root@master ~]# mysql -uroot -p123123
mysql>
MySQL搭建主从复制
master操作
mysql> grant replication slave on *.* to 'root'@'192.168.233.%' identified by '123123';
[root@master ~]# vi /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#添加以下内容
server-id=1
log-bin=mysql-bin
expire_logs_days= 7
max_binlog_size= 100m #binlog每个日志文件大小
binlog_cache_size= 4m #binlog缓存大小
max_binlog_cache_size= 512m #最大binlog缓存大小
lower_case_table_names=1 #不区分大小写
binlog-do-db=401_test #需要同步的库，可选
[root@master ~]# systemctl restart mysqld.service
mysql> show variables like 'log_%';
+----------------------------------------+--------------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------------+
| log_bin | ON | #查看是否为ON
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/log/mysqld.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| log_warnings | 1 |
+----------------------------------------+--------------------------------+
mysql> create database 401_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 401_test |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
slave操作
[root@slave ~]# vi /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#添加以下内容
log-bin=mysql-bin
binlog_format=mixed #设置混合模式复制
server-id=2
replicate-do-db=401_test #需要同步的库,可添加多个，可选
[root@slave ~]# systemctl restart mysqld.service
mysql> create database 401_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| 401_test |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
查看masterbinary位置
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 226
Binlog_Do_DB: 401_test
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
-- File:日志名称
-- Position:日志偏移量
-- Binlog_Do_DB:记录日志的库
开启slave同步
mysql>CHANGE MASTER TO
MASTER_HOST='192.168.100.107',
MASTER_USER='root',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=226;
-- HOST:主节点IP
-- USER:master授权给slave的用户名
-- PASSWORD:master授权给slave的密码
-- MASTER_LOG_FILE:master的日志名称
-- MASTER_LOG_POS:日志偏移量，需要和master一致
mysql> start slave; --开启从复制
mysql> show slave status\G --查看slave的线程是否开启
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.107
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 226
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: 401_test
注意：Slave_IO_Running为读取master的binaryLog的线程 Slave_SQL_Running为执行SQL的线程 这两个线程必须都为YES才可以实现主从复制
验证主从复制
mysql> use 401_test;
mysql> create table test1 (id int(11),name varchar(255));
mysql> insert into test1(id,name) values(1,'zhangsan');
mysql> select * from test1;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> use 401_test;
mysql> show tables;
+--------------------+
| Tables_in_401_test |
+--------------------+
| test1 |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> delete from test1 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
Empty set (0.00 sec)
mysql> select * from test1;
Empty set (0.00 sec)
https://blog.csdn.net/weixin_45826416/article/details/124593665
MySQL主从复制基于5.7
1. 环境
| 操作系统 |
IP |
主机名 |
版本 |
| centos7.9 |
192.168.233.133 |
master |
5.7.44 |
| centos7.9 |
192.168.233.134 |
slave |
5.7.44 |
2. 安装MySQL
2节点都执行
# 下载
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
# 安装
rpm -ivh mysql57-community-release-el7-9.noarch.rpm
# 执行完成后会在/etc/yum.repos.d/目录下生成两个repo文件mysql-community.repo mysql-community-source.repo,必须进入到 /etc/yum.repos.d/目录后再执行安装
cd /etc/yum.repos.d/
yum install mysql-server --nogpgcheck # nogpgcheck表示不校验key
# 启动并开机启动
systemctl start mysqld --now
3. 修改密码
# 2节点都执行
# 查找密码
grep password /var/log/mysqld.log
# 根据查找出的密码进行登录
mysql -uroot -p
# 修改密码
ALTER USER USER() IDENTIFIED BY 'Admin2023!';
4. master设置
4.1 修改配置文件
vim /etc/my.cnf
# 在mysqld区域添加
#开启log-bin二进制日志
log-bin=mysql-bin
#配置唯一的服务器ID，一般使用IP最后一位
server-id=133
#添加，允许从服务器更新二进制日志
log-slave-updates=true
########################下面不是必须配置
#主要是为了使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性
innodb_flush_log_at_trx_commit = 1
#使binlog在每1次binlog写入后与硬盘同步
sync_binlog = 1
#只保留7天的二进制日志，以防磁盘被日志占满
expire-logs-days=7
#需要同步的数据库，如果有多个，复制多份指定数据库名即可
binlog_do_db=testdb
#不需要同步的数据库，如果有多个，复制多份指定数据库名即可
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 重启mysql
systemctl restart mysqld
4.2 登录MySQL查看是否生效
# 登录mysql查看
mysql -uroot -pAdmin2023!
mysql> show variables like 'server_id'; # 查看server id
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 133 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin'; # 查看binlog是否开启
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%skip_networking%'; #skip_networking默认是OFF关闭状态，启用后，主从服务器将无法相互通信，所以这里必须是OFF
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
4.3 建立主从复制的账号
GRANT REPLICATION SLAVE ON *.* to 'slavetest'@'%' identified by 'Admin2023!';
flush privileges;
4.4 #查看master的状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 854
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
5. slave设置
5.1 修改配置文件
#配置唯一的服务器ID，一般使用IP最后一位
server-id=134
5.2 重启数据库，查看配置是否生效
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# mysql -uroot -pAdmin2023!
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 134 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
6. 配置复制参数
mysql> CHANGE MASTER TO MASTER_HOST='192.168.233.133',MASTER_USER='slavetest',MASTER_PASSWORD='Admin2023!',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=854;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.233.133
Master_User: slavetest
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 854
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7.测试
7.1 master创建数据
mysql> create database ceshi DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;;
mysql> create table ceshi.test(id int(255),name varchar(255));
mysql> insert into ceshi.test values(1,'zs');
mysql> insert into ceshi.test values(2,'ls');
mysql> select * from ceshi.test;
+------+------+
| id | name |
+------+------+
| 1 | zs |
| 2 | ls |
+------+------+
2 rows in set (0.00 sec)
7.2 slave 验证
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from ceshi.test;
+------+------+
| id | name |
+------+------+
| 1 | zs |
| 2 | ls |
+------+------+
2 rows in set (0.00 sec)
MySQL升级--小版本
检查是否是rpm安装
[root@localhost ~]# rpm -qa |grep -i mysql
mysql-community-libs-5.7.22-1.el7.x86_64
mysql-community-server-5.7.22-1.el7.x86_64
mysql-community-common-5.7.22-1.el7.x86_64
mysql-community-client-5.7.22-1.el7.x86_64
查看版本
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
解压需要更新的安装包
[root@localhost ~]# tar xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar mysql-community-client-5.7.28-1.el7.x86_64.rpm mysql-community-common-5.7.28-1.el7.x86_64.rpm mysql-community-libs-5.7.28-1.el7.x86_64.rpm mysql-community-server-5.7.28-1.el7.x86_64.rpm
备份数据库文件
navicat备份
修改参数
设置参数使数据库关闭时会清空缓存避免版本间缓存格式差异
[root@localhost ~]# mysql -uroot -p --execute="SET GLOBAL innodb_fast_shutdown=0"
Enter password: Admin@123
关闭数据库
[root@localhost ~]# systemctl stop mysqld
执行升级操作
[root@localhost ~]# rpm -Uvh mysql*.rpm --nodeps --force
启动数据库
[root@localhost ~]# systemctl start mysqld
检查新旧版本兼容
[root@localhost ~]# mysql_upgrade -uroot -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
db1.test OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.
rpm验证版本
[root@localhost ~]# rpm -qa|grep -i mysql
mysql-community-libs-5.7.28-1.el7.x86_64
mysql-community-server-5.7.28-1.el7.x86_64
mysql-community-common-5.7.28-1.el7.x86_64
mysql-community-client-5.7.28-1.el7.x86_64
查看版本
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper
登录mysql验证版本
[root@localhost ~]# mysql -uroot -p
Enter password: Admin@123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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> select version();
+-----------+
| version() |
+-----------+
| 5.7.28 |
+-----------+
1 row in set (0.00 sec)
MySQL之MHA高可用
准备环境
| 服务器 |
IP |
| MHA Manager |
192.168.58.129 |
| master |
192.168.58.130 |
| slave1 |
192.168.58.131 |
| slave2 |
192.168.58.132 |
搭建主从复制
添加hosts文件，每台都添加
[root@slave1 ~]# cat <<EOF >>/etc/hosts
192.168.58.129 mha-manager
192.168.58.130 master
192.168.58.131 slave1
192.168.58.132 slave2
EOF
master修改配置文件
[root@master ~]# vi /etc/my.cnf
server-id=1 #server-id与从服务器server-id不能重复
log-bin=master-bin #添加，主服务器开启二进制文件
log_slave-updates=true #添加，允许从服务器更新二进制文件
[root@master ~]# systemctl start mysqld #启动mysql
slave1修改配置文件
[root@slave1 ~]# vi /etc/my.cnf
server-id=2
log_bin=master-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@slave1 ~]# systemctl start mysqld
slave2修改配置文件
[root@slave2 ~]# vi /etc/my.cnf
server-id=3
log_bin=master-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@slave2 ~]# systemctl start mysqld
配置mysql一主两从
# 所有MySQL服务器进行MySQL授权
允许myslave用户在该网段使用密码Admin@123456登录
grant replication slave on *.* to 'myslave'@'192.168.58.%' identified by 'Admin@123456';
允许mha用户在该网段使用密码Admin@123456登录
grant all privileges on *.* to 'mha'@'192.168.58.%' identified by 'Admin@123456';
防止上面用户登录不上，再加三条授权主机名登录的用户
grant all privileges on *.* to 'mha'@'master' identified by 'Admin@123456';
grant all privileges on *.* to 'mha'@'slave1' identified by 'Admin@123456';
grant all privileges on *.* to 'mha'@'slave2' identified by 'Admin@123456';
flush privileges; #刷新
在master节点上查看二进制文件和位置点
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 1989 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
开启Slave从服务器功能
--2台都执行
--配置同步，注意master_log_file和master_log_pos的值要与master查询的一致
mysql> change master to master_host='192.168.58.130',master_user='myslave',master_password='Admin@123456',master_log_file='master-bin.000002',master_log_pos=1989;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave; --开启同步
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G --查看slave状态(查看I/O线程和SQL线程都为YES)
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.58.130
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1989
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes --负责与主机的io通信
Slave_SQL_Running: Yes --负责自己的slave mysql进程
slave1、slave2节点设置为只读模式
set global read_only=1;
主从复制验证
-- master创建test库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
--slave1、slave2验证是否有test库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
安装mha软件
- 所有服务器上都安装MHA以来的环境，首先安装epel，需要在线源安装,包括mha服务器
[root@mah-manager ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
[root@mah-manager ~]# yum install epel-release --nogpgcheck -y
完毕！
[root@mah-manager ~]# yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN
#mysql数据库安装时可能会报以下错误
错误：软件包：2:postfix-2.10.1-9.el7.x86_64 (@anaconda)
需要：libmysqlclient.so.18(libmysqlclient_18)(64bit)
正在安装: 1:mariadb-libs-5.5.68-1.el7.x86_64 (local)
libmysqlclient.so.18(libmysqlclient_18)(64bit)
错误：软件包：2:postfix-2.10.1-9.el7.x86_64 (@anaconda)
需要：libmysqlclient.so.18()(64bit)
可用: 1:mariadb-libs-5.5.68-1.el7.x86_64 (local)
libmysqlclient.so.18()(64bit)
错误：软件包：2:postfix-2.10.1-9.el7.x86_64 (@anaconda)
需要：libmysqlclient.so.18()(64bit)
正在安装: 1:mariadb-libs-5.5.68-1.el7.x86_64 (local)
libmysqlclient.so.18()(64bit)
错误：软件包：perl-DBD-MySQL-4.023-6.el7.x86_64 (base)
需要：libmysqlclient.so.18(libmysqlclient_18)(64bit)
可用: 1:mariadb-libs-5.5.68-1.el7.x86_64 (local)
libmysqlclient.so.18(libmysqlclient_18)(64bit)
错误：软件包：2:postfix-2.10.1-9.el7.x86_64 (@anaconda)
需要：libmysqlclient.so.18(libmysqlclient_18)(64bit)
可用: 1:mariadb-libs-5.5.68-1.el7.x86_64 (local)
libmysqlclient.so.18(libmysqlclient_18)(64bit)
错误：软件包：perl-DBD-MySQL-4.023-6.el7.x86_64 (base)
需要：libmysqlclient.so.18()(64bit)
可用: 1:mariadb-libs-5.5.68-1.el7.x86_64 (local)
libmysqlclient.so.18()(64bit)
您可以尝试添加 --skip-broken 选项来解决该问题
** 发现 2 个已存在的 RPM 数据库问题， 'yum check' 输出如下：
2:postfix-2.10.1-9.el7.x86_64 有缺少的需求 libmysqlclient.so.18()(64bit)
2:postfix-2.10.1-9.el7.x86_64 有缺少的需求 libmysqlclient.so.18(libmysqlclient_18)(64bit)
#解决，安装后重新执行以上命令
[root@master ~]# rpm -ivh mysql-community-libs-compat-5.7.38-1.el7.x86_64.rpm
所有服务器安装node组件
# 在所有服务器上必须先安装node组件，最后在MHA-manager节点上安装manager组件，因为manager依赖node组件
# 将软件包mha4mysql-node-0.57.tar.gz上传
[root@mah-manager ~]# tar xf mha4mysql-node-0.57.tar.gz
[root@mah-manager ~]# cd mha4mysql-node-0.57
[root@mah-manager mha4mysql-node-0.57]# perl Makefile.PL
[root@mah-manager mha4mysql-node-0.57]# make && make install
在MHA manager节点上安装manager组件
# 将软件包mha4mysql-manager-0.57.tar.gz上传
[root@mah-manager ~]# tar xf mha4mysql-manager-0.57.tar.gz
[root@mah-manager ~]# cd mha4mysql-manager-0.57
[root@mah-manager mha4mysql-manager-0.57]# perl Makefile.PL
[root@mah-manager mha4mysql-manager-0.57]# make && make install
- manager组件安装后在/usr/local/bin下面会生成几个工具，主要包括以下几个：
| masterha_check_ssh |
检查 MHA 的 SSH 配置状况 |
| masterha_check_repl |
检查 MySQL 复制状况 |
| masterha_manger |
启动 manager的脚本 |
| masterha_check_status |
检测当前 MHA 运行状态 |
| masterha_master_monitor |
检测 master 是否宕机 |
| masterha_master_switch |
控制故障转移（自动或者手动） |
| masterha_conf_host |
添加或删除配置的 server 信息 |
| masterha_stop |
关闭manager |
- node组件安装后也会在/usr/local/bin下面生成几个脚本(这些工具通常由MHAManager的脚本触发，无需人为操作)：主要如下：
| save_binary_logs |
保存和复制 master 的二进制日志 |
| apply_diff_relay_logs |
识别差异的中继日志事件并将其差异的事件应用于其他的 slave |
| filter_mysqlbinlog |
去除不必要的 ROLLBACK 事件 |
在所有服务器上配置无密码认证
- 在mha-manager节点上配置到所有(数据库节点)的无密码认证
[root@mah-manager ~]# ssh-keygen -t rsa # 一路回车
[root@mah-manager ~]# ssh-copy-id master
[root@mah-manager ~]# ssh-copy-id slave1
[root@mah-manager ~]# ssh-copy-id slave2
- 在master上配置到数据库节点slave1和slave2的无密码认证
[root@master ~]# ssh-keygen -t rsa
[root@master ~]# ssh-copy-id slave1
[root@master ~]# ssh-copy-id slave2
- 在slave1上配置到数据库节点master和slave2的无密码认证
[root@slave1 ~]# ssh-keygen -t rsa
[root@slave1 ~]# ssh-copy-id master
[root@slave1 ~]# ssh-copy-id slave2
- 在slave2上配置到数据库节点master和slave1的无密码认证
[root@slave2 ~]# ssh-keygen -t rsa
[root@slave2 ~]# ssh-copy-id master
[root@slave2 ~]# ssh-copy-id slave1
在manager节点上配置MHA
- 在manager节点上复制相关脚本到/usr/local/bin目录
[root@mah-manager ~]# cp -rp mha4mysql-manager-0.57/samples/scripts /usr/local/bin/
# 复制后会有四个执行文件
[root@mah-manager ~]# ll /usr/local/bin/scripts/
总用量 32
-rwxr-xr-x 1 1001 1001 3648 5月 31 2015 master_ip_failover
-rwxr-xr-x 1 1001 1001 9870 5月 31 2015 master_ip_online_change
-rwxr-xr-x 1 1001 1001 11867 5月 31 2015 power_manager
-rwxr-xr-x 1 1001 1001 1360 5月 31 2015 send_report
| master_ip_failover |
自动切换时 VIP 管理的脚本 |
| master_ip_online_change |
在线切换时 vip 的管理 |
| power_manager |
故障发生后关闭主机的脚本 |
| send_report |
因故障切换后发送报警的脚本 |
- 复制上述的自动切换时VIP管理的脚本到/usr/local/bin目录，这里使用master_ip_failover脚本来管理VIP和故障切换
[root@mah-manager ~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
- 修改内容如下：(删除原有内容，直接复制并修改vip相关参数，vip自定义)
[root@mah-manager ~]# echo ''>/usr/local/bin/master_ip_failover
[root@mah-manager ~]# vi /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
#############################添加内容部分#########################################
my $vip = '192.168.58.200'; #指定vip的地址
my $brdc = '192.168.58.255'; #指定vip的广播地址
my $ifdev = 'eth1'; #指定vip绑定的网卡
my $key = '1'; #指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #代表此变量值为ifconfig ens33:1 192.168.58.200
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #代表此变量值为ifconfig ens33:1 192.168.58.200 down
my $exit_code = 0; #指定退出状态码为0
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
## A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
- 创建MHA软件目录并赋值配置文件，使用app1.cnf配置文件来管理mysql节点服务器，配置文件一般放在/etc目录下
[root@mah-manager ~]# mkdir /etc/masterha
[root@mah-manager ~]# cp mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
- 删除原有内容，直接复制并修改节点服务器的IP地址主从1从2
[root@mah-manager ~]# echo ''>/etc/masterha/app1.cnf
[root@mah-manager ~]# vi /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/var/lib/mysql
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=Admin@123456
ping_interval=1
remote_workdir=/tmp
repl_password=Admin@123456
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.58.131 -s 192.168.58.132
shutdown_script=""
ssh_user=root
user=mha
[server1]
hostname=192.168.58.130
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.58.131
port=3306
[server3]
hostname=192.168.58.132
port=3306
[root@master ~]# ifconfig eth0:1 192.168.58.200/24
- 在manager节点上测试ssh无密码认证，如果正常最后输出successfully
[root@mah-manager ~]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Wed Mar 8 20:20:41 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 8 20:20:41 2023 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed Mar 8 20:20:41 2023 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Wed Mar 8 20:20:41 2023 - [info] Starting SSH connection tests..
Wed Mar 8 20:20:42 2023 - [debug]
Wed Mar 8 20:20:41 2023 - [debug] Connecting via SSH from root@192.168.58.130(192.168.58.130:22) to root@192.168.58.131(192.168.58.131:22)..
Wed Mar 8 20:20:41 2023 - [debug] ok.
Wed Mar 8 20:20:41 2023 - [debug] Connecting via SSH from root@192.168.58.130(192.168.58.130:22) to root@192.168.58.132(192.168.58.132:22)..
Wed Mar 8 20:20:41 2023 - [debug] ok.
Wed Mar 8 20:20:42 2023 - [debug]
Wed Mar 8 20:20:41 2023 - [debug] Connecting via SSH from root@192.168.58.131(192.168.58.131:22) to root@192.168.58.130(192.168.58.130:22)..
Wed Mar 8 20:20:42 2023 - [debug] ok.
Wed Mar 8 20:20:42 2023 - [debug] Connecting via SSH from root@192.168.58.131(192.168.58.131:22) to root@192.168.58.132(192.168.58.132:22)..
Wed Mar 8 20:20:42 2023 - [debug] ok.
Wed Mar 8 20:20:43 2023 - [debug]
Wed Mar 8 20:20:42 2023 - [debug] Connecting via SSH from root@192.168.58.132(192.168.58.132:22) to root@192.168.58.130(192.168.58.130:22)..
Wed Mar 8 20:20:42 2023 - [debug] ok.
Wed Mar 8 20:20:42 2023 - [debug] Connecting via SSH from root@192.168.58.132(192.168.58.132:22) to root@192.168.58.131(192.168.58.131:22)..
Wed Mar 8 20:20:42 2023 - [debug] ok.
Wed Mar 8 20:20:43 2023 - [info] All SSH connection tests passed successfully.
- 在manager节点上测试mysql主从连接情况，最后出现MySQL Replication Health is OK字样说明正确
[root@mah-manager ~]# masterha_check_repl -conf=/etc/masterha/app1.cnf
Wed Mar 8 20:24:35 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 8 20:24:35 2023 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Wed Mar 8 20:24:35 2023 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Wed Mar 8 20:24:35 2023 - [info] MHA::MasterMonitor version 0.57.
Wed Mar 8 20:24:36 2023 - [info] GTID failover mode = 0
Wed Mar 8 20:24:36 2023 - [info] Dead Servers:
Wed Mar 8 20:24:36 2023 - [info] Alive Servers:
Wed Mar 8 20:24:36 2023 - [info] 192.168.58.130(192.168.58.130:3306)
Wed Mar 8 20:24:36 2023 - [info] 192.168.58.131(192.168.58.131:3306)
Wed Mar 8 20:24:36 2023 - [info] 192.168.58.132(192.168.58.132:3306)
...
Checking the Status of the script.. OK
Wed Mar 8 20:24:40 2023 - [info] OK.
Wed Mar 8 20:24:40 2023 - [warning] shutdown_script is not defined.
Wed Mar 8 20:24:40 2023 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
- 在manager节点上启动MHA
[root@mah-manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
# 说明：
- -remove_dead_master_conf：该参数代表当发生主从切换后，老的主库的 ip 将会从配置文件中移除。
\- -manger_log：日志存放位置。
- -ignore_last_failover：在缺省情况下，如果 MHA 检测到连续发生宕机，且两次宕机间隔不足 8 小时的话，则不会进行 Failover， 之所以这样限制是为了避免 ping-pong 效应。该参数代表忽略上次 MHA 触发切换产生的文件，默认情况下，MHA 发生切换后会在日志记目录，也就是上面设置的日志app1.failover.complete文件，下次再次切换的时候如果发现该目录下存在该文件将不允许触发切换，除非在第一次切换后收到删除该文件，为了方便，这里设置为–ignore_last_failover。
[root@mah-manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:13235) is running(0:PING_OK), master:192.168.58.130
[root@mah-manager ~]# cat /var/log/masterha/app1/manager.log |grep "current master"
Wed Mar 8 20:26:20 2023 - [info] Checking SSH publickey authentication settings on the current master..
192.168.58.130(192.168.58.130:3306) (current master)
- 查看master的VIP地址192.168.58.200是否存在，这个VIP地址不会因manager节点停止服务而消失
[root@master ~]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.58.130 netmask 255.255.255.0 broadcast 192.168.58.255
inet6 fe80::20c:29ff:fe91:2db6 prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:91:2d:b6 txqueuelen 1000 (Ethernet)
RX packets 521219 bytes 662016029 (631.3 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 841923 bytes 2225215244 (2.0 GiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.58.200 netmask 255.255.255.0 broadcast 192.168.58.255
ether 00:0c:29:91:2d:b6 txqueuelen 1000 (Ethernet)
masterha_stop --conf=/etc/masterha/app1.cnf
MySQL二进制安装5.7
1. 下载安装包
# 创建用户
groupadd mysql &> /dev/null
useradd mysql -g mysql -M -s /sbin/nologin &> /dev/null
#下载安装包并解压
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.36-el7-x86_64.tar.gz
tar xf mysql-5.7.36-el7-x86_64.tar.gz
#创建数据目录并修改属主
mkdir /data
mv mysql-5.7.36-el7-x86_64 /data/mysql
mkdir -p /data/mysql/data
chown -R mysql:mysql /data/mysql/
2. 修改配置文件
cat > /etc/my.cnf << EOF
[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
port = 3306
socket=/tmp/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld/mysqld.pid
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
no-auto-rehash
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
server-id = 11
log-bin=master-bin
#binlog_format = MIXED
log-slave-updates=true
sync_binlog = 0
skip-external-locking
performance_schema_max_table_instances=400
table_open_cache = 2048
key_buffer_size = 512M
sort_buffer_size = 8M
net_buffer_length = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
thread_cache_size = 300
query_cache_size = 64M
tmp_table_size = 246M
query_cache_type = 1
explicit_defaults_for_timestamp = true
#skip-networking
#skip-name-resolve
max_connections = 2000
max_connect_errors = 100000
open_files_limit = 65535
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#log-bin=mysql-bin
binlog_format=row
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/data/mysql/data/mysql-slow.log
long_query_time=0.5
#log_queries_not_using_indexes=on
early-plugin-load = ""
default_storage_engine = InnoDB
innodb_data_home_dir = /data/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/
innodb_buffer_pool_size = 3G
innodb_log_file_size = 64M
innodb_log_buffer_size = 16MB
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4
EOF
3. 创建socket、log、pid文件
#创建socket文件
touch /tmp/mysql.sock
chown mysql:mysql /tmp/mysql.sock
chmod 755 /tmp/mysql.sock
#创建log文件
touch /var/log/mysqld.log
chown -R mysql:mysql /var/log/mysqld.log
chmod 755 /var/log/mysqld.log
#创建pid文件
mkdir /tmp/mysqld
touch /tmp/mysqld/mysqld.pid
chown -R mysql:mysql /tmp/mysqld
chmod 755 /tmp/mysqld/mysqld.pid
4. 启动服务
#指定目录安装mysql
cd /data/mysql/bin/
./mysqld --initialize --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
#后台安全启动
./mysqld_safe --user=mysql &
5. 修改默认密码
#获取启动密码
cat /var/log/mysqld.log | grep "temporary password" | awk '{print $NF}'
#登录mysql、修改密码
./mysql -uroot -p
alter user 'root'@'localhost' identified by '123456';
grant all privileges on *.* to 'root'@'%' identified by '123456';
FLUSH PRIVILEGES;
exit
6. 优化启动
#配置环境变量
cat >> /etc/profile << EOF
export MYSQL_HOME=/data/mysql
export PATH="$PATH:$MYSQL_HOME/bin"
EOF
source /etc/profile
#加入系统服务
cp -a /data/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add /etc/init.d/mysqld
chkconfig mysqld on
#重启mysql
pkill -9 mysqld
systemctl start mysqld
MySQL二进制5.7小版本更新
1. 下载软件包并解压
#下载安装包并解压
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-el7-x86_64.tar.gz
tar xf mysql-5.7.38-el7-x86_64.tar.gz
chown -R mysql:mysql mysql-5.7.38-el7-x86_64
2. 升级MySQL
#备份全库
mysqldump -uroot -p123456 --databases > /data/allbak.sql
cp -a /data/mysql /data/mysqlbak
#登录mysql执行慢速关闭
mysql -u root -p
select @@innodb_fast_shutdown;
SET GLOBAL innodb_fast_shutdown=0;
#关闭mysql
systemctl stop mysqld
#使用新的目录覆盖老的目录,"\"不能少，要不然亦需要手动输入yes
\cp -frp mysql-5.7.38-el7-x86_64/* /data/mysql
#启动服务，升级
systemctl start mysqld
mysql_upgrade -uroot -p
3. 测试
#登录观察版本
mysql -u root -p
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.38 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.38-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | el7 |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)
#查看数据完整性
4. 回滚
#关闭数据库
systemctl stop mysqld
#将当前数据库备份或删除
mv /data/mysql /data/mysqlbak2
#将之前的数据库
mv /data/mysqlbak /data/mysql
systemctl start mysqld
#测试
[root@localhost ~]# mysql -uroot -p
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.36 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
| version | 5.7.36-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | el7 |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)
MySQL二进制5.7升级至8.0
1. 检查升级的前置条件
#不得存在以下问题：
- 不得有使用过时数据类型或函数的表。
如果表包含 5.6.4 之前格式的 旧时间列（不支持小数秒精度的列）TIME， 则不支持就地升级到 MySQL 8.0 。如果您的表仍然使用旧的时间列格式，请在尝试就地升级到 MySQL 8.0 之前使用它们进行升级。有关更多信息，请参阅 MySQL 5.7 参考手册中的 服务器更改。 DATETIMETIMESTAMPREPAIR TABLE
- 不能有孤立.frm文件。
- 触发器不得缺少或为空的定义器或无效的创建上下文（由 character_set_client、 collation_connection、或 表Database Collation显示的属性 指示）。必须转储并恢复任何此类触发器以解决问题。 SHOW TRIGGERSINFORMATION_SCHEMA TRIGGERS
要检查这些问题，请执行以下命令：
mysqlcheck -u root -p --all-databases --check-upgrade
#不能有使用不支持本机分区的存储引擎的分区表。要识别此类表，请执行以下查询：
mysql -uroot -p
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_SCHEMA) = 'mysql'
and LOWER(TABLE_NAME) IN
(
'catalogs',
'character_sets',
'check_constraints',
'collations',
'column_statistics',
'column_type_elements',
'columns',
'dd_properties',
'events',
'foreign_key_column_usage',
'foreign_keys',
'index_column_usage',
'index_partitions',
'index_stats',
'indexes',
'parameter_type_elements',
'parameters',
'resource_groups',
'routines',
'schemata',
'st_spatial_reference_systems',
'table_partition_values',
'table_partitions',
'table_stats',
'tables',
'tablespace_files',
'tablespaces',
'triggers',
'view_routine_usage',
'view_table_usage'
);
#查询报告的任何表都必须更改为使用 InnoDB或不分区。要将表存储引擎更改为InnoDB，请执行以下语句：
ALTER TABLE table_name ENGINE = INNODB;
#要使分区表不分区，请执行以下语句：
ALTER TABLE table_name REMOVE PARTITIONING;
#MySQL 5.7 mysql系统数据库中不能有与 MySQL 8.0 数据字典使用的表同名的表。要识别具有这些名称的表，请执行以下查询：
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
(SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);
#不能有外键约束名称超过 64 个字符的表。使用此查询来识别约束名称过长的表：
SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';
2. 下载软件包并解压
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.25-el7-x86_64.tar.gz
tar xf mysql-8.0.25-el7-x86_64.tar.gz
chown -R mysql:mysql mysql-8.0.25-el7-x86_64
mv mysql-8.0.25-el7-x86_64 /data/mysql8
3. 升级MySQL
#备份全库
mysqldump -uroot -p123456 --databases > /data/allbak.sql
cp -a /data/mysql /data/mysqlbak
#登录mysql执行慢速关闭
mysql -u root -p
select @@innodb_fast_shutdown;
SET GLOBAL innodb_fast_shutdown = 0; -- slow shutdown
#关闭mysql
systemctl stop mysqld
#修改my.cnf
vi /etc/my.cnf
basedir=/data/mysql8
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
#query_cache_size = 64M
#query_cache_type = 1
4. 修改环境变量
vi /etc/profile
export MYSQL_HOME=/data/mysql8
export PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/bin:/data/mysql8/bin"
5. 登录测试
# 启动
systemctl start mysqld
#登录查看版本
./mysql -uroot -p
select version();
#检查数据完整性
MySQL8.0安装(二进制)
1. 卸载Centos原生自带的mariadb
rpm -e `rpm -qa |grep mariadb` --nodeps
2. 解压mysql
tar xf mysql-8.0.33-el7-x86_64.tar.gz -C /usr/local/
mv /usr/local/mysql-8.0.33-el7-x86_64/ /usr/local/mysql
3. 建立用户并授权
这里将数据放在了/usr/local/mysql/data,如果需要更改修改初始化时的--datadir=参数和/etc/my.cnf的datadir和socket
mkdir -p /usr/local/mysql/data
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql /usr/local/mysql/
chmod -R 755 /usr/local/mysql/
4. 初始化mysql
[root@localhost bin]# pwd
/usr/local/mysql/bin
[root@localhost bin]# ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql --lower-case-table-names=1
2023-09-21T07:46:27.155032Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.33) initializing of server in progress as process 2376
2023-09-21T07:46:27.164365Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-09-21T07:46:27.417107Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-09-21T07:46:28.078332Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ;ZYpo9qGfefr
--lower-case-table-names 表示不区分大小写
5. 修改配置文件
[root@localhost bin]# touch /etc/my.cnf
[root@localhost bin]# vim /etc/my.cnf
[root@localhost bin]# cat /etc/my.cnf
[client]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
default-character-set=utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect ='SET NAMES utf8mb4'
port = 3306
socket = /usr/local/mysql/data/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
datadir = /usr/local/mysql/data
lower_case_table_names=1
innodb_strict_mode=0
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[root@localhost bin]# chmod 644 /etc/my.cnf
[root@localhost bin]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
. SUCCESS!
[root@localhost bin]# ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@localhost bin]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@localhost bin]# ln -s /usr/local/mysql/mysql.sock /var/mysql.sock
[root@localhost bin]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
6. 修改密码，并设置远程登录
[root@localhost bin]# mysql -uroot -p
Enter password: ;ZYpo9qGfefr
mysql> mysql> alter user 'root'@'localhost' identified by 'mysql@2023!';
mysql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;
7. 设置MySQL看机启动
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --list
mysqld 0:关 1:关 2:开 3:开 4:开 5:开 6:关
MySQL8.0安装
1. 删除mariadb
rpm -e `rpm -qa|grep mariadb` --nodeps
2. 下载并安装MySQL源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
3. 使用yum安装MySQL
yum -y install mysql-community-server
4. 启动MySQL
systemctl start mysqld ## 启动MySQL
systemctl status mysqld ## 查看MySQL运行状态
systemctl stop mysqld ## 停止MySQL
systemctl restart mysqld ## 重启MySQL
systemctl enable mysqld ## 设置开机自启动MySQL
netstat -utpln |grep 3306 ## 查看端口是否存在
5. 修改MySQL密码
# 查看默认密码
grep "password" /var/log/mysqld.log
[root@localhost ~]# mysql -uroot -p
Enter password: 输入从日志查看密码
# MySQL默认密码规范等级为MEDIUM，即需要满足密码长度大于8位，包含数字、大小写和特殊符号。
# 如果有改变密码强度需求，想把密码设置简单一些，可以修改MySQL8的默认密码规范登记以及密码最短长度，事先也需要按照默认规范登记先修改密码，否则不允许操作数据库
set global validate_password.policy = 0; # 设置密码规范等级为0；
set global validate_password.length = 4; # 设置密码最短长度为4；
# 修改用户密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Hanweb@123.';
# 此处用root用户作示例，用户名任意
# 创建远程访问root用户
mysql> create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Hanweb@123.';
# wei'gai
mysql> grant all on *.* to 'root'@'%';
mysql> flush privileges;
MySQL主从+Mycat读写分离
| 名称 |
IP |
应用 |
| server |
192.168.233.133 |
mysql-server |
| slave1 |
192.168.233.134 |
mysql-server |
| slave2 |
192.168.233.135 |
mysql-server |
| mycat |
192.168.233.136 |
mycat+jdk |
| client |
192.168.233.137 |
mysql |
1. 安装数据库(略)
2. 安装jdk
# 解压jdk
[root@mycat ~]# tar xf jdk-8u171-linux-x64.tar.gz
[root@mycat ~]# mv jdk1.8.0_171/ /usr/local/java
# 配置环境变量
[root@mycat ~]# vim /etc/profile
export JAVA_HOME=/usr/local/java
export PATH=$PATH:$JAVA_HOME/bin
# 使生效
[root@mycat ~]# source /etc/profile
# 验证是否生效
[root@mycat ~]# java -version
3. 安装Mycat
# 解压Mycat
[root@mycat ~]# tar xf tar xf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz
[root@mycat ~]# mv mycat/ /usr/local/mycat
#查看
[root@mycat ~]# ls /usr/local/mycat
bin catlet conf lib logs version.txt
4. 在master节点授权
允许Mcat程序连接MySQL集群，slave节点会同步权限
[root@server ~]# mysql -uroot -p123123
grant all on *.* to 'admin'@'192.168.233.136' identified by '123123';
flush privileges;
5. 配置Mycat并启动
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="VIR" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" ></schema>
<dataNode name="dn1" dataHost="node1" database="ceshi" />
<dataHost name="node1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.233.133" url="192.168.233.133:3306" user="admin" password="123123">
<readHost host="192.168.233.134" url="192.168.233.134:3306" user="admin" password="123123" />
<readHost host="192.168.233.135" url="192.168.233.135:3306" user="admin" password="123123" />
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml # 从97行左右开始改动
<user name="admin" defaultAccount="true">
<property name="password">123123</property>
<property name="schemas">VIR</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">VIR</property>
<property name="readOnly">true</property>
</user>
[root@mycat ~]# vim /usr/local/mycat/conf/log4j2.xml
25 <asyncRoot level="debug" includeLocation="true">
# 配置Mycat环境变量
vim /etc/profile
export MYCAT_HOME=/usr/local/mycat
export PATH=$MYCAT_HOME/bin:$PATH
# 使生效
source /etc/profile
# 启动Mycat
mycat start
[root@mycat ~]# netstat -utpln |grep 066
tcp6 0 0 :::8066 :::* LISTEN 11287/java
tcp6 0 0 :::9066 :::* LISTEN 11287/java
[root@mycat ~]# tail /usr/local/mycat/logs/wrapper.log ##服务日志
[root@mycat ~]# tail /usr/local/mycat/logs/mycat.log ##读写分离日志
6. server创建表
create table ceshi.test (id int,name char(16));
7. 客户端测试数据读写分离
mysql -uadmin -p123123 -h 192.168.233.136 -P 8066
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| VIR |
+----------+
1 row in set (0.00 sec)
MySQL [(none)]> use VIR;
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 [VIR]> show tables;
+-----------------+
| Tables_in_ceshi |
+-----------------+
| test |
+-----------------+
1 row in set (0.00 sec)
MySQL [VIR]> insert into ceshi.test values(1,"zs");
Query OK, 1 row affected (0.00 sec)
MySQL [VIR]> insert into ceshi.test values(2,"ls");
Query OK, 1 row affected (0.01 sec)
MySQL [VIR]> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | zs |
| 2 | ls |
+------+------+
2 rows in set (0.00 sec)
8. 客户端查看数据读写请求分布
[root@client ~]# mysql -uadmin -p123123 -h 192.168.233.136 -P 9066
MySQL [(none)]> show @@datasource;
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | 192.168.233.133 | mysql | 192.168.233.133 | 3306 | W | 0 | 10 | 1000 | 39 | 0 | 2 |
| dn1 | 192.168.233.134 | mysql | 192.168.233.134 | 3306 | R | 0 | 4 | 1000 | 31 | 1 | 0 |
| dn1 | 192.168.233.135 | mysql | 192.168.233.135 | 3306 | R | 0 | 4 | 1000 | 33 | 3 | 0 |
+----------+-----------------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+
3 rows in set (0.01 sec)
