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
2)slave运行2个线程
-
Slave_io:复制master主机binlog日志文件里的sql到本机的relay-log文件里
-
Slave_sql:执行本机relay_log文件里的sql语句,重现master的数据操作
主从同步原理图:
2,构建主从同步
1)基本思路
-
确保数据相同:从库必须要有主库上的数据
-
配置主服务器:启用binlog日志,授权用户,查看当前正在使用的日志
-
配置从服务器:设置server_id(唯一),指定主库信息
-
测试配置:客户端连接主库写入数据,在从库上也能查询到
2)确保数据一致
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的性能。