mysql循序渐进
单机部署
1、linux部署
1、卸载mariadb相关包
[root@zabbix-10 iso]# yum remove -y mariadb*
2、安装mysql
[root@zabbix-10 iso]# tar -xvf mysql-5.7.10-1.el7.x86_64.rpm-bundle.tar
[root@zabbix-10 iso]# ls *.rpm
mysql-community-client-5.7.10-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.10-1.el7.x86_64.rpm
mysql-community-server-5.7.10-1.el7.x86_64.rpm
mysql-community-test-5.7.10-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.10-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.10-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.10-1.el7.x86_64.rpm
mysql-community-common-5.7.10-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.10-1.el7.x86_64.rpm
mysql-community-libs-5.7.10-1.el7.x86_64.rpm
mysql-community-embedded-5.7.10-1.el7.x86_64.rpm
mysql-community-devel-5.7.10-1.el7.x86_64.rpm
[root@zabbix-10 iso]# rm -rf *minimal*
[root@zabbix-10 iso]# yum -y install mysql*.rpm
[root@zabbix-10 iso]# systemctl start mysqld
[root@zabbix-10 iso]# ss -tunlp | grep 3306
tcp LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=35140,fd=22))
3、设置MySQL字符集
[root@zabbix-10 ~]# vim /etc/my.cnf
[mysqld]
character-set-server=utf8
[root@zabbix-10 ~]# systemctl restart mysqld
4、修改默认密码
- 查看初始密码
[root@zabbix-10 ~]# grep 'temporary password' /var/log/mysqld.log
2021-05-22T18:07:41.764166Z 1 [Note] A temporary password is generated for root@localhost: q6f6wa_#pftP
- 登录及修改密码
登录之后 第一步必须先修改密码，否则其他操作都执行不了。
[root@zabbix-10 ~]# mysql -p
Enter password:
mysql> alter user root@localhost identified by 'Jluo520!';
Query OK, 0 rows affected (0.00 sec)
//新修改的密码中 必须包含 大小写字母数字及符号
2、docker部署
vi docker-compose.yml
version: '2'
services:
mysql:
hostname: mysql
container_name: mysql
image: mysql:5.7
privileged: true
environment:
MYSQL_USER: yunwisdom
MYSQL_PASSWORD: password123
MYSQL_DATABASE: database
MYSQL_ROOT_PASSWORD: password123
ports:
- 3306:3306
volumes:
- /etc/localtime:/etc/localtime:ro
- ./data:/var/lib/mysql:rw
- /etc/hosts:/etc/hosts:rw
docker-compose up
启动docker-compose（后台模式-不打印日志）
docker-compose up -d
mysql多实例
什么是多实例：
在一台物理主机上运行多个数据库服务，可以节约运维成本，提高硬件利用率
mysql地址：https://downloads.mysql.com/archives/community/
1）解压软件、修改目录名
[root@mysql ~]# cd mysql/
[root@mysql mysql]# ls
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql mysql]# tar -xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@mysql mysql]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql
2）调整PATH变量
[root@mysql mysql]# echo "export PATH=/usr/local/mysql/bin:$PATH" \
>> /etc/profile
[root@mysql mysql]# source /etc/profile
[root@mysql mysql]# echo $PATH
/usr/local/mysql/bin:/usr/local/mycat/bin:/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin
3）编辑主配置文件/etc/my.cnf
每个实例要有独立的数据库目录、监听端口号、实例名称和独立的sock文件
[mysqld_multi] //启用多实例
mysqld = /usr/local/mysql/bin/mysqld_safe //指定进程文件路径
mysqladmin = /usr/local/mysql/bin/mysqladmin //指定管理命令路径
user = root //指定进程用户
[mysqld1] //实例进程名称
port=3307 //端口号
datadir=/data3307 //数据库目录 ，要手动创建
socket=/data3307/mysqld.sock //指定sock文件的路径和名称
pid-file=/data3307/mysql1.pid //进程pid号文件位置
log-error=/data3307/mysql1.err //错误日志位置
[mysqld2]
port=3308
datadir=/data3308
socket=/data3308/mysqld.sock
pid-file=/data3308/mysql2.pid
log-error=/data3308/mysql2.err
4）创建数据库目录
[root@mysql mysql]# mkdir -p /data3307
[root@mysql mysql]# mkdir -p /data3308
5）创建进程运行的所有者和组 mysql
[root@mysql mysql]# useradd mysql
[root@mysql mysql]# chown mysql:mysql /data*
6）初始化授权库
[root@mysql mysql]# mysqld --user=mysql --basedir=/usr/local/mysql
--datadir=/data3307 --initialize
...
2018-09-26T07:07:33.443378Z 1 [Note] A temporary password is generated for root@localhost: 7L?Vi!dGKmgu //root用户登录的初始化密码
[root@mysql mysql]# mysqld --user=mysql --basedir=/usr/local/mysql
--datadir=/data3308 --initialize
...
2018-09-26T07:08:07.770289Z 1 [Note] A temporary password is generated for root@localhost: kC)BbyUp1a-b //root用户登录的初始化密码
7）启动多实例
[root@mysql mysql]# mysqld_multi start 1 //1为实例编号
[root@mysql mysql]# mysqld_multi start 2
8）查看端口
[root@mysql mysql]# netstat -utnlp | grep :3307
tcp6 0 0 :::3307 :::* LISTEN 21009/mysqld
[root@mysql mysql]# netstat -utnlp | grep :3308
tcp6 0 0 :::3308 :::* LISTEN 21177/mysqld
[root@mysql mysql]# ps -C mysqld
PID TTY TIME CMD
21009 pts/1 00:00:00 mysqld
21177 pts/1 00:00:00 mysqld
9）访问多实例
使用初始化密码登录多实例1
[root@mysql mysql]# mysql -u root -p'7L?Vi!dGKmgu' -S /data3307/mysqld.sock
mysql> alter user root@"localhost" identified by '123456'; //修改密码
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
使用初始化密码登录多实例2
[root@mysql bin]# mysql -u root -p'kC)BbyUp1a-b' -S /data3307/mysqld.sock
mysql> alter user root@"localhost" identified by '123456'; //修改密码
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
10）创建库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
11）停止启动的实例服务
mysqld_multi --user=root --password=密码 stop 实例编号
[root@mysql mysql]# mysqld_multi --user=root --password=123456 stop 1
[root@mysql mysql]# netstat -utnlp | grep :3307 //查看没有端口
[root@mysql mysql]# mysqld_multi --user=root --password=123456 stop 2
[root@mysql mysql]# netstat -utnlp | grep :3308 //查看没有端口
[root@mysql mysql]# mysql -uroot -p123456 -S /data3307/mysqld.sock
//拒绝连接
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 '/data3307/mysqld.sock' (2)
主从同步
1，主从同步原理
1）master,记录数据更改操作
-
启用binlog日志
-
设置binlog日志格式
-
设置server_id
２）slave运行2个线程
-
Slave_io：复制master主机binlog日志文件里的sql到本机的relay-log文件里
-
Slave_sql:执行本机relay_log文件里的sql语句，重现master的数据操作
主从同步原理图：
2，构建主从同步
１）基本思路
-
确保数据相同：从库必须要有主库上的数据
-
配置主服务器：启用binlog日志，授权用户，查看当前正在使用的日志
-
配置从服务器：设置server_id（唯一），指定主库信息
-
测试配置：客户端连接主库写入数据，在从库上也能查询到
２）确保数据一致
Masterr服务器
备份所有库
Slave服务器
清空同名库（如果有的话）
离线导入由master提供的备份
Server_id n #1~255
提示：Relay-log是中继日志文件
3、配置
1、方案
使用2台RHEL 7虚拟机，如图-1所示。其中192.168.4.10是MySQL主服务器，负责提供同步源；另一台192.168.4.20作为MySQL从服务器，通过调取主服务器上的binlog日志，在本地重做对应的库、表，实现与主服务器的AB复制（同步）。
提前为两台MySQL服务器安装好MySQL-server、MySQL-Client软件包，并为数据库用户root修改密码；Linux客户机上则只需安装MySQL-Client软件包即可。
提示：
为了在启用binlog日志及同步之前保持主、从库的一致性，建议进行初始化——备份主服务器上现有的库，然后导入到从服务器上。
当现有库、表都采用MyISAM引擎时，可执行离线备份、恢复，这样更有效率；否则，可通过mysqldump等工具来实现库的导出、导入
2、配置MySQL Master（主服务器，192.168.4.10）
1）修改/etc/my.cnf配置，重新启动MySQL服务程序
指定服务器ID号、允许日志同步：
[root@dbsvr1 mysql]# vim /etc/my.cnf
[mysqld]
log_bin=dbsvr1-bin //启用binlog日志，并指定文件名前缀
server_id = 10 //指定服务器ID号
......
重启mysql服务：
[root@dbsvr1 ~]# systemctl restart mysqld.service
2）新建一个备份用户，授予复制权限
需要的权限为REPLICATION SLAVE，允许其从Slave服务器访问：
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicater'@'192.168.4.%' IDENTIFIED BY 'pwd123';
Query OK, 0 rows affected, 1 warning (0.09 sec)
3）检查Master服务器的同步状态
在已经初始化现有库的情况下，查看MASTER状态，记录下当前的日志文件名、偏移的位置（下面SLAVE发起复制时需要用到）：
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: dbsvr1-bin.000001 //记住当前的日志文件名
Position: 154 //记住当前的位置
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
3、配置MySQL Slave（从服务器，192.168.4.20）
1）修改/etc/my.cnf配置，重新启动MySQL服务程序
指定服务器ID号、允许日志同步：
[root@dbsvr2 ~]# vim /etc/my.cnf
[mysqld]
log_bin=dbsvr2-bin //启动SQL日志，并指定文件名前缀
server_id = 20 //指定服务器ID号，不要与Master的相同
.. ..
在生产环境中，还可以根据需要设置更详细的同步选项。比如，指定当主、从网络中断时的重试超时时间（slave-net-timeout=60 ）等，具体可参考MySQL手册。
配置完成后，重启mysql服务：
[root@dbsvr2 ~]# systemctl restart mysqld.service
通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.4.10',
-> MASTER_USER='replicater',
-> MASTER_PASSWORD='pwd123',
-> MASTER_LOG_FILE='dbsvr1-bin.000002', //对应Master的日志文件
-> MASTER_LOG_POS=334; //对应Master的日志偏移位置
Query OK, 0 rows affected, 2 warnings (0.12 sec)
然后执行START SLAVE（较早版本中为SLAVE START）启动复制：
mysql> START SLAVE; //启动复制
Query OK, 0 rows affected (0.00 sec)
注意：一旦启用SLAVE复制，当需要修改MASTER信息时，应先执行STOP SLAVE停止复制，然后重新修改、启动复制。
通过上述连接操作，MASTER服务器的设置信息自动存为master.info文件，以后每次MySQL服务程序时会自动调用并更新，无需重复设置。查看master.info文件的开头部分内容，可验证相关设置：
[root@dbsvr2 ~]# ls -lh /var/lib/mysql/master.info
-rw-r-----. 1 mysql mysql 132 4月 23 12:06 /var/lib/mysql/master.info
[root@dbsvr2 ~]# head /var/lib/mysql/master.info
25
dbsvr1-bin.000001
154
192.168.4.10
replicater
pwd123
3306
60
0
2）检查Slave服务器的同步状态
通过SHOW SLAVE STATUS语句可查看从服务器状态，确认其中的IO线程、SQL线程正常运行，才能成功同步：
mysql> SHOW SLAVE STATUS\G
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.1
Master_User: replicater
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dbsvr1-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: db2-relay-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: dbsvr1-bin.000001
Slave_IO_Running: Yes //IO线程应该已运行
Slave_SQL_Running: Yes //SQL线程应该已运行
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 2490
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 2d4d8a11-27b7-11e7-ae78-52540055c180
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
若START SLAVE直接报错失败，请检查CHANGE MASTER相关设置是否有误，纠正后再重试；若IO线程或SQL线程有一个为“No”，则应检查服务器的错误日志，分析并排除故障后重启主从复制。
4、测试主从同步效果
1）在Master上操作数据库、表、表记录
新建newdb库、newtable表，随意插入几条表记录：
mysql> CREATE DATABASE newdb; //新建库newdb
Query OK, 1 row affected (0.17 sec)
mysql> USE newdb; //切换到newdb库
Database changed
mysql> CREATE TABLE newtable(id int(4)); //新建newtable表
Query OK, 0 rows affected (0.46 sec)
mysql> INSERT INTO newtable VALUES(1234),(5678); //插入2条表记录
Query OK, 2 rows affected (0.24 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM newtable; //确认表数据
+------+
| id |
+------+
| 1234 |
| 5678 |
+------+
2 rows in set (0.00 sec)
2）在Slave上确认自动同步的结果
直接切换到newdb库，并查询newtable表的记录，应该与Master上的一样，这才说明主从同步已经成功生效：
mysql> USE newdb; //直接切换到newdb库
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> SELECT * FROM newtable; //输出表记录
+------+
| id |
+------+
| 1234 |
| 5678 |
+------+
2 rows in set (0.02 sec)
3）在Master服务器上可查看Slave主机的信息
mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 2 | | 3306 | 10 | 512cf7c1-27c4-11e7-8f4b-5254007b030b |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
互为主从
读写分离
前言
1）同步软件：
- MaxScale：https://github.com/mariadb-corporation/MaxScale/tags
- MaxScale：https://mariadb.com/kb/en/maxscale/
2）方案
使用4台RHEL 7虚拟机，如图-1所示。其中192.168.4.10和192.168.4.20，分别提供读、写服务，均衡流量，通过主从复制保持数据一致性，由MySQL代理192.168.4.100面向客户端，收到SQL写请求时，交给服务器A处理，收到SQL读请求时，交给服务器B处理。linux客户机用于测试配置，可以使用真机代替
搭建主从
1）搭建一主一从结构，主库192.168.4.10上面操作
[root@master10 ~]# vim /etc/my.cnf
[mysqld]
server_id=10 //指定服务器ID号
log-bin=master10 //启用binlog日志，并指定文件名前缀
...
[root@master10 ~]# systemctl restart mysqld //重启mysqld
2）从库192.168.4.20上面操作
[mysqld]
server_id=20 //指定服务器ID号，不要与Master的相同
log-bin=slave20 //启动SQL日志，并指定文件名前缀
read_only=1 //只读模式
...
[root@slave20 ~]# systemctl restart mysqld
3）主库授权一个用户并查看master的状态
[root@master10 ~]# mysql -u root -p123456
mysql> grant all on *.* to 'replicater'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master10.000002 | 449 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4）从库通过CHANGE MASTER语句指定MASTER服务器的IP地址、同步用户名/密码、起始日志文件、偏移位置（参考MASTER上的状态输出）
[root@slave20 ~]# mysql -u root -p123456
mysql> change master to master_host='192.168.4.10',
-> master_user='replicater',
-> master_password='123456',
-> master_log_file='master10.000002',
-> master_log_pos=738;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.10
Master_User: replicater
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master10.000002
Read_Master_Log_Pos: 738
Relay_Log_File: slave20-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master10.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 738
Relay_Log_Space: 528
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: 95ada2c2-bb24-11e8-abdb-525400131c0f
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
5）测试，主库创建aa库
mysql> create database aa;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
6）从库上面查看，有aa库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aa |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
实现mysql读写分离
1）配置数据读写分离服务器192.168.4.100
环境准备关闭防火墙和SElinux，保证yum源可以正常使用
[root@maxscale ~]# cd mysql/
[root@maxscale mysql]# ls
maxscale-2.1.2-1.rhel.7.x86_64.rpm
[root@maxscale mysql]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
//安装maxscale
warning: maxscale-2.1.2-1.rhel.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 8167ee24: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:maxscale-2.1.2-1 ################################# [10
2）配置maxscale
[root@maxscale mysql]# vim /etc/maxscale.cnf.template
[maxscale]
threads=auto //运行的线程的数量
[server1] //定义数据库服务器
type=server
address=192.168.4.10 //数据库服务器的ip
port=3306
protocol=MySQLBackend //后端数据库
[server2]
type=server
address=192.168.4.20
port=3306
protocol=MySQLBackend
[MySQL Monitor] //定义监控的数据库服务器
type=monitor
module=mysqlmon
servers=server1, server2 //监控的数据库列表，不能写ip
user=scalemon //监视数据库服务器时连接的用户名scalemon
passwd=123456 //密码123456
monitor_interval=10000 //监视的频率 单位为秒
#[Read-Only Service] //不定义只读服务器
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#passwd=mypwd
#router_options=slave
[Read-Write Service] //定义读写分离服务
type=service
router=readwritesplit
servers=server1, server2
user=maxscaled //用户名 验证连接代理服务时访问数据库服务器的用户是否存在
passwd=123456 //密码
max_slave_connections=100%
[MaxAdmin Service] //定义管理服务
type=service
router=cli
#[Read-Only Listener] //不定义只读服务使用的端口号
#type=listener
#service=Read-Only Service
#protocol=MySQLClient
#port=4008
[Read-Write Listener] //定义读写服务使用的端口号
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener] //管理服务使用的端口号
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4099 //手动添加，不指定时使用的是默认端口在启动服务以后可以知道默认端
3）根据配置文件的设置，在数据库服务器上添加授权用户（主库执行，从库查看）
mysql> grant replication slave,replication client on *.* to scalemon@'%' identified by "123456"; //监控数据库服务器时，连接数据库服务器的用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant select on mysql.* to maxscaled@"%" identified by "123456";
//验证 访问数据时，连接数据库服务器使用的用户，是否在数据库服务器上存在的，连接用户
Query OK, 0 rows affected, 1 warning (0.01 sec)
4）查看授权用户
在主库上面查看
mysql> select user,host from mysql.user where user in ("scalemon","maxscaled");
+-----------+------+
| user | host |
+-----------+------+
| maxscaled | % |
| scalemon | % |
+-----------+------+
2 rows in set (0.00 sec)
在从库上面查看
mysql> select user,host from mysql.user where user in ("scalemon","maxscaled");
+-----------+------+
| user | host |
+-----------+------+
| maxscaled | % |
| scalemon | % |
+-----------+------+
2 rows in set (0.00 sec)
测试授权用户
[root@maxscale mysql]# mysql -h 192.168.4.10 -u scalemon -p123456
[root@maxscale mysql]# mysql -h 192.168.4.20 -u scalemon -p123456
[root@maxscale mysql]# mysql -h 192.168.4.10 -u maxscaled -p123456
[root@maxscale mysql]# mysql -h 192.168.4.20 -u maxscaled -p123456
5）启动服务
[root@maxscale ~]# maxscale -f /etc/maxscale.cnf
[root@maxscale ~]# ps -C maxscale //查看进程
PID TTY TIME CMD
17930 ? 00:00:00 maxscale
[root@maxscale ~]# netstat -antup | grep maxscale //查看端口
tcp6 0 0 :::4099 :::* LISTEN 17930/maxscale
tcp6 0 0 :::4006 :::* LISTEN 17930/maxscale
6）测试，在本机访问管理端口查看监控状态
maxadmin -P端口 -u用户名 -p密码
[root@maxscale ~]# maxadmin -P4099 -uadmin -pmariadb
MaxScale>
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 192.168.4.10 | 3306 | 0 | Master, Running
server2 | 192.168.4.20 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
7）在客户端访问读写分离服务器（没有mysql命令可以安装）
mysql -h读写分离服务ip -P4006 -u用户名 -p密码
[root@slave53 ~]# mysql -h192.168.4.100 -P4006 -ureplicater -p123456
mysql> select @@hostname; //查看当前主机名
+------------+
| @@hostname |
+------------+
| slave20 |
+------------+
1 row in set (0.00 sec)
mysql> create table t2(id int(4) );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into aa.t2 values(777);
Query OK, 1 row affected (0.01 sec)
在主库上面查看
mysql> use aa
mysql> select * from t2;
+------+
| id |
+------+
| 777 |
+------+
1 row in set (0.00 sec)
从库（主库同步到从库）
mysql> use aa
mysql> select * from t2;
+------+
| id |
+------+
| 777 |
+------+
1 row in set (0.00 sec)
mha集群
mysql备份与恢复
参考：https://www.cnblogs.com/wuqiuyin/p/15412286.html
1、备份的类型
按照备份时数据库的运行状态，可以分为三种，分别是：冷备、温备、热备。
- 冷备：停库、停服务来备份，即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线。
- 温备：不停库、不停服务来备份，会(锁表)阻止用户的写入，即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作 。
- 热备：不停库、不停服务来备份，也不会(锁表)阻止用户的写入 即当数据库进行备份时, 数据库的读写操作均不是受影响 。
MySQL中进行不同类型的备份还要考虑存储引擎是否支持？
常用的备份工具
2、冷备
直接cp复制或者tar打包
3、温备
1、使用mysqldump
语法:
mysqldump -h 服务器 -u用户名 -p密码 选项与参数 > 备份文件.sql
常用参数
- -A --all-databases：导出全部数据库
- -Y --all-tablespaces：导出全部表空间
- -B --databases：导出几个数据库。参数后面所有名字参量都被看作数据库名。
- --default-character-set：设置默认字符集，默认值为utf8
- --force：在导出过程中忽略出现的SQL错误。
- -d --no-data：不导出任何数据，只导出数据库表结构。
- -p --password：连接数据库密码
- -P --port：连接数据库端口号
- -u --user：指定连接的用户名。
- --triggers：备份触发器数据（现在都是开发写触发器）
2、备份
mysqldump -u root -p userdb > userdb.sql
mysqldump -u root -p -B mysql userdb > mysql+test+userdb.sql
3、恢复
1）创建名为userdb2的新库
mysql> CREATE DATABASE userdb2;
Query OK, 1 row affected (0.00 sec)
2）导入备份文件，在新库中重建表及数据
[root@dbsvr1 ~]# mysql -u root -p userdb2 < /root/userdb.sql
Enter password: //验证口令
3）确认新库正常，启用新库
mysql> USE userdb2; //切换到新库
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> SELECT sn,username,uid,gid,homedir //查询数据，确认可用
-> FROM userlist LIMIT 10;
+----+----------+-----+-----+-----------------+
| sn | username | uid | gid | homedir |
+----+----------+-----+-----+-----------------+
| 1 | root | 0 | 0 | /root |
| 2 | bin | 1 | 1 | /bin |
| 3 | daemon | 2 | 2 | /sbin |
| 4 | adm | 3 | 4 | /var/adm |
| 5 | lp | 4 | 7 | /var/spool/lpd |
| 6 | sync | 5 | 0 | /sbin |
| 7 | shutdown | 6 | 0 | /sbin |
| 8 | halt | 7 | 0 | /sbin |
| 9 | mail | 8 | 12 | /var/spool/mail |
| 10 | operator | 11 | 0 | /root |
+----+----------+-----+-----+-----------------+
10 rows in set (0.00 sec)
4）废弃或删除旧库
mysql> DROP DATABASE userdb;
Query OK, 2 rows affected (0.09 sec)
4、热备
mysql优化
1、性能优化
1）查看服务运行时的参数配置
mysql> show variables\G;
mysql> show variables like "%innodb%";
2）并发连接数量
查看当前已经使用的连接数
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like "Max_used_connections";
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 3 |
+----------------------+-------+
1 row in set (0.00 sec)
查看默认的最大连接数
mysql> show variables like "max_connections%";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
3）连接超时时间
mysql> show variables like "%timeout%";
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.00 sec)
4）允许保存在缓存中被重用的线程数量
mysql> show variables like "thread_cache_size";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+
1 row in set (0.00 sec)
5）用于MyISAM引擎的关键索引缓存大小
mysql> show variables like "key_buffer_size";
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)
6）为每个要排序的线程分配此大小的缓存空间
mysql> show variables like "sort_buffer_size";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)
7）为顺序读取表记录保留的缓存大小
mysql> show variables like "read_buffer_size";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.01 sec)
8）为所有线程缓存的打开的表的数量
mysql> show variables like "table_open_cache";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 2000 |
+------------------+-------+
1 row in set (0.00 sec)
2、查询优化
1）常用日志种类及选项，如图-1所示：
记录慢查询，图-2所示：
启用慢查询日志
[root@master10 ~]# vim /etc/my.cnf
...
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=5
log_queries_not_using_indexes=1
...
[root@master10 ~]# systemctl restart mysqld
2）查看慢查询日志
[root@master10 ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log
Reading mysql slow query log from /var/lib/mysql/mysql-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
查看缓存的大小
mysql> show variables like "query_cache%";
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
5 rows in set (0.00 sec)
3）查看当前的查询缓存统计
mysql> show global status like "qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 40 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
安全
等保测评中提到需设置数据库安全策略，建议设置登录失败5次，锁定账户10分钟。
windows：
install plugin CONNECTION_CONTROL soname 'connection_control.dll';
install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.dll';
linux
install plugin CONNECTION_CONTROL soname 'connection_control.so';
install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';
执行：
SET GLOBAL connection_control_failed_connections_threshold = 5;
SET GLOBAL connection_control_min_connection_delay = 600000;
常见sql
增加索引
1、添加主键索引（PRIMARY KEY）
ALTER TABLE table_name ADD PRIMARY KEY ( column)
2、添加普通索引（INDEX）
ALTER TABLE table_name ADD INDEX index_name ( column )
3、添加唯一索引（UNIQUE）
ALTER TABLE table_name ADD UNIQUE (column)
4、添加全文索引（FULLTEXT）
ALTER TABLE table_name ADD FULLTEXT ( column)
5、添加复合索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
6、删除索引
DROP INDEX index_name ON table
7、查看索引的语法格式如下
SHOW INDEX FROM <表名> [ FROM <数据库名>]
基本操作
指标
存储引擎
存储引擎InnoDB与Myisam的六大区别
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持。
占用空间小、不支持事务、不支持外键、只支持表级锁。
事务：
innodb支持回滚、支持事务、外键、行锁，多版本事务管理mvcc。
myisam不支持事务、不支持外键、表级锁。
存储结构上：
innodb 2个文件.ifm（表结构） 、.ibd(数据和索引)
myisam 3个文件 .ifm（表结构） 、.myd(数据文件)、.myi（索引文件)
存储空间上：
innodb 占用空间大，需要更多的内存和存储，他需要有缓存池用于高速缓存数据和索引。
myisam 占用空间小，可被压缩，数据以文件的方式存储。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
索引:
myisam支持全文索引fulltext，索引保存的是行地址。
innodb可以使用sphinx插件支持全文索引，主键索引保存的是数据，其他索引保存的是主键索引地址。
缓存：
myisam仅仅缓存索引，不缓存数据（myisam数据文件和索引文件分开）
innodb缓存索引和真实数据，所以，对内存有更高的要求。
性能：
select：myisam性能高
update、insert：innodb性能高
清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
为什么MyISAM会比Innodb 的查询速度快？
INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多； 1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少； 2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快 3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护
如何选择？
如果执行大量的SELECT，MyISAM是更好的选择。
如果你的数据执行大量的INSERT或UPDATE ，出于性能方面的考虑，应该使用InnoDB表。
等保修复
账户密码
1、密码过期时间
# 90天 临时
mysql> SET GLOBAL default_password_lifetime = 90
# 永久
[mysqld]
default_password_lifetime=90
# 查询
mysql> SHOW variables LiKE 'default_password_lifetime';
2、登录失败锁定用户
每个平台的文件名后缀有所不同(对于 Unix 和类 Unix 系统为 .so ，对于 Windows 为 .dll )。下面以 Linux 系统为例来安装下 connection_control 插件，Windows 系统只需要将 .so 改成 .dll 即可。
# 动态安装 connection_control 插件
mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
# 验证插件状态
mysql> SELECT PLUGIN_NAME,PLUGIN_STATUS from INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%';
# 安装完成后 可以看到相关系统变量
mysql> show variables like 'connection_control%';
mysql> SET GLOBAL connection_control_failed_connections_threshold = 5; ## 失败尝试次数为：5次
mysql> SET GLOBAL connection_control_min_connection_delay = 600000; ## 单位默认为毫秒, 设置为：10分钟
mysql> SET GLOBAL connection_control_max_connection_delay = 2147483647; ## 单位默认为毫秒，约 25 天
- connection_control_failed_connections_threshold：允许帐户进行的连续失败尝试的次数。默认为 3 ，表示当连接失败 3 次后启用连接控制，0 表示不开启。
- connection_control_max_connection_delay：超出阈值的连接失败的最大延迟(以毫秒为单位)，默认 2147483647 毫秒，约 25 天。
- connection_control_min_connection_delay：超过阈值的连接失败的最小延迟(以毫秒为单位)，默认 1000 毫秒，即 1 秒。
3、用户空闲操作自动退出时间
SET GLOBAL wait_timeout = 1800;
SET GLOBAL interactive_timeout = 1800;
SHOW GLOBAL VARIABLES like 'wait_timeout';
SHOW GLOBAL VARIABLES like '%interactive_timeout%';
日志
FAQ
1、忘记 mysql 数据库连接密码
vim /etc/my.cnf
[mysqld]
skip-grant-tables
mysql -uroot -p
mysql>use mysql
mysql>update user set password=password("123456") where user="root";
mysql>flush privileges;
mysql>quit
2、mysql启动报错 "unknown variable 'defaults-file=/etc/my
使用指定的my.cnf,而不用默认的/etc/my.cnf文件，可以在启动时，在mysqld_safe后加上参数--default-file=/usr/local/server/mysql2/etc/my.cnf，但是要注意的是，主参数必须紧接着mysqld_safe后面，如果做第二个或者第二个以后的参数加入时，则会出现如下类似错误错误：
/usr/local/server/mysql/libexec/mysqld: unknown variable 'defaults-file=/usr/local/server/mysql2/etc/my.cnf' 且服务无法启动！
这是mysql的一个bug！
3、these UUIDs must be different for replication to w
show slave status\G 发现有下面报错，导致复制起不来
…
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
…
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
…
尝试删除mysql数据目录下的auto.cnf文件，然后重新启动mysql，之后同步就正常了，auto.cnf会重新生成。
cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=0845599c-d994-11ec-a37f-000c29561530
4、mysql内存占用率高
MySQL内存占用率过高可能会导致MySQL运行缓慢或崩溃。为了解决这个问题，可以通过优化MySQL配置文件、优化MySQL查询语句、定期清理MySQL日志文件和升级MySQL版本等方法来提高MySQL的性能。
1、查看MySQL内存占用率
2、优化MySQL配置文件
3、优化MySQL查询语句
MySQL查询语句（慢查询语句）的优化也是解决MySQL内存占用率过高的问题的一个重要方面。以下是一些优化MySQL查询语句的技巧：
-
避免使用SELECT *
-
使用索引
-
避免使用子查询
-
避免使用OR操作符
-
避免使用LIKE操作
4、定期清理MySQL日志文件
log、error log和slow query log等。这些日志文件会占用大量的磁盘空间，从而导致MySQL内存占用率过高。因此，需要定期清理这些日志文件，以释放磁盘空间。
5、升级MySQL版本
如果以上方法都无法解决MySQL内存占用率过高的问题，那么可以考虑升级MySQL版本。新版本的MySQL通常会修复一些已知的性能问题，从而提高MySQL的性能。
