MySQL数据库快速入门与应用实战(阶段一)
MySQL数据库快速入门与应用实战(阶段一)
|
作者 |
刘畅 |
|
时间 |
2020-09-02 |
实验环境说明：
系统：centos7.5
|
主机名 |
ip |
配置 |
|
|
slavenode3 |
172.16.1.123 |
4核/8G/60G |
|
目录
1 mysql概述
1.1 mysql是什么
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发。MySQL是一个可移植的数据库，几乎能在当前所有的操作系统上运行，
例如Unix/Linux、Windows、Mac和Solaris。
MySQL在2008年1月被Sun收购，在2009年4月Sun又被Oracle收购。
MySQL自称是世界上最流行的开源数据库，实际也是如此。
(1) MySQL是一个数据库管理系统
(2) MySQL数据库是关系型的
(3) MySQL软件是开源的
(4) MySQL数据库性能高、可靠、可扩展、易于使用
(5) 客户端/服务器架构系统
至今，最新的版本是v8。v5.7.1在2013年4月23日发布，是一个里程碑。经过2年多的不断完善和更新，在2015年10月发布v5.7.9GA版本。目前生产环境用的最多的MySQL版本：5.1、5.5和5.6、5.7 。
1.2 5.7版本新增功能
安全性增强
InnoDB 功能增强
JSON 支持
sys 模式 （ 性能数据收集 ）
多源复制
多线程复制
支持在线变更复制方式
等 ..
1.3 mysql体系结构
1.4 mysql主流分支
1 MySQL
原生团队开发维护。
2 MariaDB
由MySQL创始人开发维护，保证开放源码，功能特性也更强一些。大部分功能及参数保持高度与MySQL兼容。
MySQL5.1->MariaDB5.1/5.2/5.3
MySQL5.5->MariaDB5.5/10.0
兼容性说明：https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility
3 Percona Server
完全兼容MySQL，开放源码，MySQL的增强版。自调整算法和对高性能硬件支持可提供出色的性能和可靠性。
1.5 主流关系型数据库
1 Oracle
甲骨文公司的一款关系数据库管理系统。是全世界上最流行的，一种高性能、高可靠性、高吞吐量的数据库解决方案。可运行在Unix/Linux、Windows等平台上。
2 Microsoft SQL Server
微软公司推出的关系型数据库管理系统。只能运行在Windows平台上。
3 PostgreSQL
加州大学伯克利分校开发关系数据库管理系统。
2 linux安装mysql
2.1 yum安装mysql5.7.31
1 部署mysql5.7.31
MySQL yum源库下载地址：https://dev.mysql.com/downloads/repo/yum/
# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
# yum clean all
# yum -y install yum-utils
# yum-config-manager --disable mysql80-community
# yum-config-manager --enable mysql57-community
# yum install mysql-community-server mysql-community-devel -y
# rpm -qa | grep mysql
mysql-community-common-5.7.31-1.el7.x86_64
mysql-community-libs-5.7.31-1.el7.x86_64
mysql-community-client-5.7.31-1.el7.x86_64
mysql-community-server-5.7.31-1.el7.x86_64
mysql-community-devel-5.7.31-1.el7.x86_64
mysql80-community-release-el7-1.noarch
mysql-community-libs-compat-5.7.31-1.el7.x86_64
2 修改mysql配置文件
# vim /etc/my.cnf
[client]
port = 3306
default-character-set = utf8
socket = /var/lib/mysql/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /usr
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
bind-address = 0.0.0.0
pid-file = /var/run/mysqld/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
log-error = /var/log/mysqld.log
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/lib/mysql/mysql-slow.log
max_connections = 10240
open_files_limit = 65535
innodb_buffer_pool_size = 3G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
transaction_isolation = READ-COMMITTE
default-storage-engine = innodb
innodb_file_per_table = on
symbolic-links = 0
explicit_defaults_for_timestamp = 1
skip-name-resolve
lower_case_table_names = 1
server-id = 1
[mysqldump]
quick
max_allowed_packet = 32M
3 启动mysql
# systemctl start mysqld
# systemctl enable mysqld
# netstat -tunlp | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3620/mysqld
4 重新设置root账号密码
# grep 'temporary password' /var/log/mysqld.log
2020-09-03T14:55:40.986342Z 1 [Note] A temporary password is generated for root@localhost: (27O8uT:Qtr!
# mysql -uroot -p'(27O8uT:Qtr!'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql5.7@LC2020';
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit;
2.2 二进制安装mysql5.7.31
1 前期准备
# yum install -y libaio
(2) 解压安装包
安装包下载地址：https://dev.mysql.com/downloads/mysql/
# tar -xzf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
# mv mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql/
# groupadd -g 1600 mysql
# useradd -g mysql -u 1600 -M -s /sbin/nologin mysql
# id mysql
uid=1600(mysql) gid=1600(mysql) 组=1600(mysql)
(4) 创建存放数据库文件的目录并赋予mysql安装目录的属主和属组都是mysql
# mkdir -p /usr/local/mysql/data/
# chown -R mysql.mysql /usr/local/mysql/
# cat /etc/my.cnf
[client]
port = 3306
default-character-set = utf8
socket = /usr/local/mysql/data/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
bind-address = 0.0.0.0
pid-file = /usr/local/mysql/data/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
log-error = /usr/local/mysql/data/mysqld.log
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /usr/local/mysql/data/mysql-slow.log
max_connections = 10240
open_files_limit = 65535
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
transaction_isolation = READ-COMMITTE
default-storage-engine = innodb
innodb_file_per_table = on
symbolic-links = 0
explicit_defaults_for_timestamp = 1
skip-name-resolve
lower_case_table_names = 1
server-id = 1
[mysqldump]
quick
max_allowed_packet = 32M
3 初始化mysql 数据库
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/usr/local/mysql/data/mysqld.pid
TimeoutSec=0
PermissionsStartOnly=true
# ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize --pid-file=/usr/local/mysql/data/mysqld.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
# systemctl start mysqld.service
# systemctl enable mysqld.service
# netstat -tunlp | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1793/mysqld
# cat >> /etc/profile << EOF
export PATH=/usr/local/mysql/bin/:\$PATH
EOF
# source /etc/profile
6 修改数据库密码
# grep 'temporary password' /usr/local/mysql/data/mysqld.log
2020-09-03T15:36:48.970741Z 1 [Note] A temporary password is generated for root@localhost: Kf;Aj:zNk7&3
# mysql -uroot -p'Kf;Aj:zNk7&3'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql5.7@2020';
mysql> exit;
2.3 编译安装mysql5.7.31
系统要求：
安装GCC编译器
安装CMake构建框架
安装Boost C++库5.7.31版本（必须此版本）
提示：编译安装时，服务器的cpu数要大于2核，内存要大于4G，否则编译的过程中可能会报错。
下载地址：https://dev.mysql.com/downloads/mysql/
2 安装依赖包
# yum install -y gcc gcc-c++ cmake bison ncurses-devel openssl-devel
3 解压软件包
# tar -xzf mysql-5.7.31.tar.gz
# tar -xzf mysql-boost-5.7.31.tar.gz
# ls -l
drwxr-xr-x 36 7161 31415 4096 6月 2 19:17 mysql-5.7.31
解压后的两个压缩包文件都放到了mysql-5.7.31 同一个目录之中
4 编译安装
# cd mysql-5.7.31/
# cmake \
-DMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_TCP_PORT=3306 \
-DEFAULT_CHARSET=utf8 \
-DEFAULT_COLLATION=utf8_general_ci \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock \
-DWITH_BOOST=./boost \
-DWITH_SYSTEMD=1
# make -j 4
# make install
5 配置my.cnf 文件
# cat /etc/my.cnf
[client]
port = 3306
default-character-set = utf8
socket = /usr/local/mysql/data/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
bind-address = 0.0.0.0
pid-file = /usr/local/mysql/data/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
log-error = /usr/local/mysql/data/mysqld.log
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /usr/local/mysql/data/mysql-slow.log
max_connections = 10240
open_files_limit = 65535
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
transaction_isolation = READ-COMMITTE
default-storage-engine = innodb
innodb_file_per_table = on
symbolic-links = 0
explicit_defaults_for_timestamp = 1
skip-name-resolve
lower_case_table_names = 1
server-id = 1
[mysqldump]
quick
max_allowed_packet = 32M
6 初始化数据
(1) 创建mysql 用户
# groupadd -g 1600 mysql
# useradd -g mysql -u 1600 -M -s /sbin/nologin mysql
# id mysql
uid=1600(mysql) gid=1600(mysql) 组=1600(mysql)
(2) 创建存放数据库文件的目录并赋予mysql安装目录的属主和属组都是mysql
# mkdir -p /usr/local/mysql/data/
# chown -R mysql.mysql /usr/local/mysql/
(3) 初始化数据库
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
7 将mysql 服务加入到systemctl
# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/usr/local/mysql/data/mysqld.pid
TimeoutSec=0
PermissionsStartOnly=true
# ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize --pid-file=/usr/local/mysql/data/mysqld.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
8 启动mysql 数据库
# systemctl daemon-reload
# systemctl start mysqld.service
# systemctl enable mysqld.service
# netstat -tunlp | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1793/mysqld
# cat >> /etc/profile << EOF
export PATH=/usr/local/mysql/bin/:\$PATH
EOF
# source /etc/profile
9 修改数据库密码
# grep 'temporary password' /usr/local/mysql/data/mysqld.log
2020-09-03T16:46:33.609008Z 1 [Note] A temporary password is generated for root@localhost: r<p/pMC*C4jT
# mysql -uroot -p'r<p/pMC*C4jT'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql5.7@2020';
mysql> exit;
2.4 mysql多实例安装
多实例不同之处
l 数据目录
l 端口
l Socket文件
l Pid文件
1 实验说明
在二进制安装mysql5.7.31基础上进行操作
关闭mysql
# systemctl stop mysqld
# systemctl disable mysqld
移除my.cnf配置文件
# mv /etc/my.cnf /tmp/
删除数据库的数据目录
# rm -rf /usr/local/mysql/data/
移除mysqld.service
# mv /usr/lib/systemd/system/mysqld.service /tmp/
2 创建mysql多实例3306、3307所需的目录
# mkdir -p /data/{3306,3307}
# mkdir -p /data/3306/data
# mkdir -p /data/3307/data
# tree /data/
/data/
├── 3306
│ └── data
└── 3307
└── data
4 directories, 0 files
# id mysql
uid=1600(mysql) gid=1600(mysql) 组=1600(mysql)
# chown -R mysql.mysql /data/
3 配置mysql多实例3306、3307的my.cnf文件
# cat /data/3306/my.cnf
[client]
port = 3306
default-character-set = utf8
socket = /data/3306/data/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/3306/data
socket = /data/3306/data/mysql.sock
bind-address = 0.0.0.0
pid-file = /data/3306/data/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
log-error = /data/3306/data/mysqld.log
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /data/3306/data/mysql-slow.log
max_connections = 10240
open_files_limit = 65535
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
transaction_isolation = READ-COMMITTE
default-storage-engine = innodb
innodb_file_per_table = on
symbolic-links = 0
explicit_defaults_for_timestamp = 1
skip-name-resolve
lower_case_table_names = 1
server-id = 1
[mysqldump]
quick
max_allowed_packet = 32M
(2) mysql 3307 实例
# cat /data/3307/my.cnf
[client]
port = 3307
default-character-set = utf8
socket = /data/3307/data/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3307
basedir = /usr/local/mysql
datadir = /data/3307/data
socket = /data/3307/data/mysql.sock
bind-address = 0.0.0.0
pid-file = /data/3307/data/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
log-error = /data/3307/data/mysqld.log
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /data/3307/data/mysql-slow.log
max_connections = 10240
open_files_limit = 65535
innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M
transaction_isolation = READ-COMMITTE
default-storage-engine = innodb
innodb_file_per_table = on
symbolic-links = 0
explicit_defaults_for_timestamp = 1
skip-name-resolve
lower_case_table_names = 1
server-id = 1
[mysqldump]
quick
max_allowed_packet = 32M
4 配置mysql多实例3306、3307的systemctl管理文件
(1) mysql 3306 实例
# cat /usr/lib/systemd/system/mysqld-3306.service
[Unit]
Description=MySQL Server 3306
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/3306/data/mysqld.pid
TimeoutSec=0
PermissionsStartOnly=true
# ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --daemonize --pid-file=/data/3306/data/mysqld.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql-3306
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
(2) mysql 3307 实例
# cat /usr/lib/systemd/system/mysqld-3307.service
[Unit]
Description=MySQL Server 3307
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/3307/data/mysqld.pid
TimeoutSec=0
PermissionsStartOnly=true
# ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf --daemonize --pid-file=/data/3307/data/mysqld.pid $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql-3307
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
(3) 使配置生效
# systemctl daemon-reload
5 初始化mysql多实例3306、3307
(1) mysql 3306 实例
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
2020-09-04T15:24:58.754532Z 1 [Note] A temporary password is generated for root@localhost: hYMl4NHvYK&j
(2) mysql 3307 实例
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
2020-09-04T15:25:06.881967Z 1 [Note] A temporary password is generated for root@localhost: QobD/0L)F?jr
6 启动mysql多实例3306、3307
(1) mysql 3306 实例
# systemctl start mysqld-3306.service
# systemctl enable mysqld-3306.service
# netstat -tunlp | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2612/mysqld
(2) mysql 3307 实例
# systemctl start mysqld-3307.service
# systemctl enable mysqld-3307.service
# netstat -tunlp | grep 3307
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 3317/mysqld
7 修改mysql多实例3306、3307的密码
(1) mysql 3306 实例
# mysql -S /data/3306/data/mysql.sock -uroot -p'hYMl4NHvYK&j'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql5.7@2020';
mysql> show global variables like "port";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> exit;
(2) mysql 3307 实例
# mysql -S /data/3307/data/mysql.sock -uroot -p'QobD/0L)F?jr'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql5.7@2021';
mysql> show global variables like "port";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> exit;
3 mysql服务器基本管理
3.1 连接到mysql服务器
# mysql --host=localhost --user=myname --password=mypass mydb
# mysql -h localhost -u myname -pmypass mydb
# mysql --host=localhost --user=myname --password mydb
# mysql -h localhost -u myname -p mydb
# mysql --host=remote.example.com --port=3306
# mysql --port=3306 --host=localhost
# mysql --port=3306 --host=127.0.0.1
# mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
# mysql -u root -p -e "SELECT VERSION();SELECT NOW()"
说明：
(1) 在linux系统中使用 mysql 命令连接数据库时 -h<mysql IP地址>、-P<mysql端口号>、-u<mysql用户名>、-p<mysql用户密码>，以上命令除用户名密码必须和指令连接在一块之外，其它输入可以和指令分开或合并都可以。默认：-hlocalhost -P3306
(2) Windows系统使用 Navicat for MySQL客户端工具连接数据库。
3.2 mysql 命令行工具命令
|
用法 |
描述 |
|
charset charset_name， \C charset_name |
更改默认字符集并发出SET NAMES语句。 |
|
connect [db_name host_name]]， \r [db_name host_name]] |
重连服务器 |
|
delimiter str， \d str |
更改mysql解释SQL语句之间的分割符，默认时分号。 |
|
edit， \e |
编辑上一步输入的语句 |
|
ego， \G |
将当前语句发送到要执行的服务器，并使用垂直格式显示结果。 |
|
use db_name， \u db_name |
使用db_name作为默认数据库 |
|
go， \g |
将当前语句发送到要执行的服务器 |
|
prompt [str]， \R [str] |
重置mysql提示符，默认时mysql> |
|
resetconnection， \x |
重新连接以清空会话状态，当mysql设置变量后使用。 |
|
source file_name， \. file_name |
读取名称文件并执行其中包含的语句 |
|
status， \s |
输出正在使用的连接和服务器的状态信息 |
|
system command， \! command |
使用默认shell解释器执行命令 |
|
tee [file_name]， \T [file_name] |
记录语句及输出，所有的数据都会附加到给定的文件中 |
|
exit，quit， \q |
退出mysql |
3.3 系统数据库
1 information_schema
存储其他数据库信息。没有数据库目录。
2 mysql
系统自身的一些数据，账户授权、事件、用户定义函数、插件信息、时区、
帮助文档和复制表。
3 performance_schema
v5.5新增的一个性能监控引擎，用于收集实例运行时内部情况。
4 sys
收集实例性能数据，以便调试和诊断问题。
3.4 数据库管理
1 查看所有数据库
# mysql> show databases;
2 查看当前默认数据库
# mysql> select database();
3 创建数据库
# mysql> create database test;
4 更改默认数据库
# mysql> use test;
5 删除数据库
# mysql> drop database test;
6 查看数据库下所有的表
# mysql> use mysql;
# mysql> show tables;
7 查看表结构
# mysql> describe mysql.plugin;
8 查看当前登录的用户
mysql> select current_user();
3.5 系统变量
1 系统变量分为
(1) 全局变量
作用域为整个实例，使用set global <var_name>=<values>语句更改变量值。
(2) 会话变量
作用于客户端会话，使用set session <var_name>=<values>语句更改变量值。
(3) 变量的修改需要考虑 可动态更改|不可动态更改、全局|局部，可参考官方文档
系统变量表：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
动态系统变量表：https://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html
2 查看系统变量
系统默认设置或者dba调整优化后的参数，静态的。可以通过set或者修改my.cnf配置文件修改。
mysql> show global | session variables;
mysql> show global | session variables like "character_set%";
3 查看系统状态变量
便于dba查看mysql当前运行的状态，做出相应优化，动态的，不可修改，只能系统自动update。
mysql> show global | session status\G;
3.6 日志类型
1 日志说明
|
Log Type |
Information Written to Log |
|
Error log |
实例启动、运行或关闭问题 |
|
General query log |
建立客户端连接和从客户端接收的语句(所有接收的语句) |
|
Binary log |
更改数据的语句(也用于复制) |
|
Relay log |
从服务器复制主服务器接收到的数据更改语句 |
|
Slow query log |
查询花费超过log_query_time(秒)执行的语句 |
2 日志配置
# vim /etc/my.cnf
[ mysqld ]
# error log
log-error = /var/log/mysqld/mysqld.log
# general query log
general_log = 1
general_log_file = /var/log/mysqld/general_query.log
# binary log
log-bin = /var/log/mysqld/mysql-bin
binlog_cache_size = 4M
binlog_format = mixed
max_binlog_cache_size = 8M
max_binlog_size = 512M
expire_logs_days = 14
# slow query log
slow_query_log = 1
long_query_time = 10
slow_query_log_file = /var/log/mysqld/slow_query.log
# relay log
relay-log = /var/log/mysqld/relay-log
max_relay_log_size = 512M
relay-log-purge = 1
3.7 字符集
1说明
字符集：Unicode
编码：GB2312、GBK、UTF-8
2 更改全局数据库字符集
set global character_set_database=utf8;
3 更改连接字符集
更改数据库客户端字符集
set global character_set_client=utf8;
更改连接默认字符集
set global character_set_connection=utf8;
4 更改存在数据库的字符集
alter database mydb character set utf8;
5更改存在表的字符集
alter table mytable default character set utf8;
6更改存在表字段字符集
alter table mytable change old_column new_column varchar(255) character set utf8;
4 存储引擎
针对于数据库中的表
4.1 InnoDB
1 InnoDB是一种高可靠和高性能的存储引擎，使用最为广泛。MySQL5.5版本之后的默认存储引擎。
2 InnoDB的主要特点
(2) 行级锁
(3) 支持外键约束
(4) 自维护缓冲池，缓存索引和数据
(5) 支持全文索引
(6) 如果服务器软硬件故障，能自动恢复崩溃之前未提交完成的更改
(7) 已经设计在处理大数据时能发挥cpu最大性能
3 物理结构
表结构文件扩展名：.frm
独立表空间表数据存放文件扩展名：.ibd
共享表空间表数据存放文件：ibdata1
mysql.engine_cost表示例：
*.ibd、ibdata1这两种文件都是存放Innodb 数据的文件，之所以有两种文件来存放Innodb 的数据（包括索引），是因为Innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和MyISAM 数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。
4 日志文件
ib_logfileN
5 启动选项/系统变量
mysql> show global variables like "innodb%";
4.2 MyISAM
myisam 是MySQL5.5版本之前默认的存储引擎
1 物理结构
表结构文件扩展名：.frm
数据文件扩展名：.MYD
索引文件扩展名：MYI
mysql.columns_priv表示例：
4.3 其他存储引擎
MEMORY
CSV
ARCHIVE
BLACKHOLE
MRG_MYISAM(MERGE)
NDB
4.4 设置存储引擎
1 在my.cnf中指定默认存储引擎
[mysqld]
default-storage-engine = InnoDB
2 在运行时指定默认存储引擎
mysql> set global default_storage_engine = InnoDB;
3 修改表存储引擎
mysql> alter table t1 engine = InnoDB;
4 创建新表时指定存储引擎
mysql> CREATE TABLE t1 (i INT) ENGINE = INNODB;
5 设置当前会话默认存储引擎
mysql> SET default_storage_engine = InnoDB;
4.5 ACID
ACID指数据事务正确执行的四个基本要素的缩写
整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执
行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
2l一致性(Consistency)
事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务多少。
3 隔离性(Isolation)
在同一时间执行多个事务，执行相同的功能，事务隔离性将确保每个事务在系统中只有该事务在使
用系统。为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用
于同一数据。
4 持久性(Durability)
事务完成后，该事务对数据库所有的更改便持久到保存数据库之中，并不会被回滚。
4.6锁机制
锁分为：表级锁，行级锁
1 InnoDB行级锁分为
共享锁(S，shared)：允许持有读取行的事务。
独占锁(X，exclusive)：也称为排它锁。允许持有更新或删除行的事务。如果持有X锁不能再加其
他类型锁。
2 行级锁优缺点
优点：高并发时减少操作冲突，事务回滚减少改变数据，可长时间加锁一个行。
缺点：比表级锁消耗更多的资源，高并发时加锁慢。
3 死锁检测和回滚
(1) innodb_deadlock_detect
在5.7.15版本加入的自动死锁检测，对产生的死锁回滚事务或打破死锁，默认是ON开启。
(2) innodb_lock_wait_timeout
事务获取资源等待最长时间，默认50秒。超过这个时间还未分配到资源就会返回失败。
(3) innodb_rollback_on_timeout
默认OFF，当发生锁等待超时，当前语句会被回滚（而不是整个事务）。要想使整个事务回滚，启动MySQL服务时指定此选项。
4 死锁解决方法
(1) mysql> show processlist;
(2)查看information_schema库中的INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS表
(3) show engine innodb status\G;
5 避免死锁方法
(1) 如果频繁出现死锁警告，可以启用innodb_print_all_deadlocks选项收集更多的调试信息。会记录在错误日志中，调试完后记得关闭。
(2) 如果事务因为死锁而失败，务必重新发布事务。
(3) 尽量保持事务时间短，不容易发送碰撞。
(4) 当事务中修改多个表或一个表多行时，按照一致的顺序执行这些操作，这样就会形成明确的队列，防止出现头尾相接的循环等待资源关系。
5 用户与权限管理
5.1 访问权限系统
1 mysql数据库提供了几个授权表：user、db、tables_priv、columns_priv、procs_priv、proxies_priv
可以间接的使用账户管理语句操作授权表，例如CREATE USER、GRANT以及REVOKE。
也可以使用INSERT、UPDATE或DELETE直接修改授权表，不建议这种方式。
2 访问认证分为两个阶段：
阶段1：连接认证
阶段2：请求认证
5.2 用户账户管理
(1) 添加用户账户
0) 查看用户
mysql> select user,host from mysql.user;
1) 创建用户后授权
示例一：
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
# WITH GRANT OPTION 表示该用户拥有创建用户的权限，和mysql root用户拥有相同的权限。
示例二：
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
2) 创建用户和赋权用一条命令完成
mysql> GRANT ALL PRIVILEGES ON *.* TO 'chang'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
3) 对特定数据库访问权限
示例一：
mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.* TO 'custom'@'localhost';
mysql> FLUSH PRIVILEGES;
示例二：
mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO 'custom'@'host47.example.com';
mysql> FLUSH PRIVILEGES;
示例三：
mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'password';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.* TO 'custom'@'%.example.com';
mysql> FLUSH PRIVILEGES;
4) 查看账户权限
mysql> show grants for 'finley'@'localhost';
5) 查看账户非特权属性
mysql> SHOW CREATE USER 'finley'@'localhost'\G;
6) mysql终端修改账户密码
mysql> ALTER USER 'finley'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
7) linux命令行修改账户密码
mysqladmin -h'localhost' -P'3306' -u'finley' -p'password' password "123456"
(2) 删除用户账户
mysql> DROP USER 'finley'@'localhost';
mysql> flush privileges;
(3) 账户资源限制
1) 可以对个别账户进行限制使用MySQL服务器
账户每小时可以发出的查询数量
账户每小时可以更新的次数
账户每小时可以连接到服务器的次数
一个账户同时连接到服务器的数量
2) 创建账户时设置资源限制
mysql> CREATE USER 'lc'@'localhost' IDENTIFIED BY '123456'
WITH
MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;
mysql> FLUSH PRIVILEGES;
mysql> show create user lc@'localhost'\G;
3) 更改现有账户限制
mysql> ALTER USER 'lc'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
mysql> FLUSH PRIVILEGES;
4) 删除限制，直接改为0即可
mysql> ALTER USER 'lc'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
(4) 密码管理
1) default_password_lifetime
默认密码到期时间。在5.7.11之前默认值365，5.7.11之后默认值为0，也就是禁用创建账户时设置密码过期时间。
mysql> show global variables like "default_password_lifetime";
2) 创建用户时设置密码过期时间
mysql> CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
mysql> FLUSH PRIVILEGES;
mysql> show create user 'jeffrey'@'localhost'\G;
3) 修改已存在用户的密码过期时间
mysql> ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 180 DAY;
4) 禁用密码到期
mysql> CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
mysql> FLUSH PRIVILEGES;
5) 使用全局到期策略
mysql> CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
mysql> ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
(5) 用户账户锁定
1) 创建用户时
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password' ACCOUNT LOCK;
mysql> FLUSH PRIVILEGES;
2) 更改已有用户
mysql> ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK;
mysql> FLUSH PRIVILEGES;
5.3 账户密码忘记处理
linux系统下重置root账户密码
1 方法一
(1) 停止MySQL 服务
# kill `cat /usr/local/mysql/data/mysqld.pid`
(2) 创建一个密码赋值语句的文本文件
# vim /tmp/mysql-init
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass!6';
(3) 使用--init-file选项启动MySQL服务
# mysqld --defaults-file=/etc/my.cnf --init-file=/tmp/mysql-init --user=mysql &
(4) 删除文本文件，使用新密码连接MySQL
# rm -f /tmp/mysql-init
# mysql -uroot -p'MyNewPass!6'
(6) 停止MySQL服务并正常启动
# kill `cat /usr/local/mysql/data/mysqld.pid`
# systemctl start mysqld
2 方法二
(1) 停止MySQL服务
# kill `cat /usr/local/mysql/data/mysqld.pid`
(2) 使用--skip--grant--tables --skip--networking选项启动MySQL服务
# mysqld --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking --user=mysql &
(3) 无密码连接MySQL
# mysql -uroot
(4) 重置密码
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql5.7@2020';
mysql> exit;
(5) 停止MySQL服务并正常启动
# kill `cat /usr/local/mysql/data/mysqld.pid`
# systemctl start mysqld
6 sql语句基础
6.1 SQL语句分类
1 DML (Data Manipulation Language，数据操作语言)
SELECT、UPDATE、INSERT、DELETE，用于对数据库中的数据进行操作。
2 DDL (Data Definition Language，数据定义语言)
DDL要比DML要多，主要有CREATE、ALTER、DROP等，用于定义或改变表结构。
3 DCL (Data Control Language，数据控制语言)
主要有GRANT、REVOKE、COMMIT、ROLLBACK等，用于设置或更改用户账户或权限。
6.2 数据类型与属性
1 数据类型
2 属性
3 JSON数据类型
mysql> create table t1 (id int, jdoc JSON);
mysql> insert into t1 values('1','{"a": "1", "b": "2"}');
mysql> select * from t1;
mysql> select jdoc->"$.b" from t1;
# 不显示反斜杠与周围的引号
mysql> select jdoc->>"$.b" from t1;
# 判断JSON类型
mysql> SELECT JSON_TYPE('["a", "b", 1]');
mysql> SELECT JSON_TYPE('"hello"');
# 转换成数组
mysql> SELECT JSON_ARRAY('a', 1, NOW());
# 转换成对象
mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
6.3 SQL基本操作
1 创建表
mysql> create table user(id int auto_increment primary key,username varchar(50) not null,password varchar(100) not null);
2 插入
mysql> insert into user(id,username,password) values("1","zhangsan","123456");
mysql> insert into user(username,password) values("lisi","1234567");
mysql> insert into user values(3,"wanger","1234568");
3 查询
mysql> select * from user;
mysql> select * from user where username='lisi';
mysql> select username,password from user;
4 更新
(1) 更新表数据
mysql> update user set password="123456" where username="lisi";
(2) 更新表字段
mysql> alter table user change password passwd varchar(30);
# password代表原字段名称，passwd代表新字段名称。
mysql> alter table user add age int;
mysql> alter table user modify username varchar(30) not null;
5 删除
(1) 删除表数据
mysql> delete from user where id=3;
(2) 删除表字段
mysql> alter table user drop age;
(3) 删除表
mysql> drop table user;
6 多表查询
(1) 创建用户表
mysql> create table user(id int auto_increment primary key,username varchar(50) not null,password varchar(100) not null);
# 插入实验数据
mysql> insert into user(username,password) values("zhangsan","1"),("lisi","2"),("wanger","3"),("xiaoming","4"),("xiaohong","5");
# 查看数据
mysql> select * from user;
(2) 创建用户信息表
mysql> create table user_info(id int auto_increment primary key,age int not null,sex varchar(10) not null);
# 插入实验数据
mysql> insert into user_info(age,sex) values(30,"nv"),(40,"man"),(50,"man"),(60,"man"),(70,"nv");
# 查看数据
mysql> select * from user_info;
(3) 查询用户所有信息
mysql> select user.username,user_info.age,user_info.sex from user,user_info where user.id=user_info.id;
(4) 查询用户年龄大于50岁的人所有信息(表别名)
mysql> select u.username,ui.age,ui.sex from user AS u,user_info AS ui where u.id=ui.id and age > 50;
(5) 查询用户年龄大于50岁并且性别是男性的人所有信息(表别名)
mysql> select u.username,ui.age,ui.sex from user AS u,user_info AS ui where u.id=ui.id and age > 50 and sex="man";
6.4 常用子句运算符
WHERE：指定条件
AND和OR运算符：组合多个条件
BETWEEN运算符：两个值之间范围数据
LIKE：通配符匹配。%表示零个、一个或多个字符。_表示一个单独的数字或字符。
LIMIT：获取记录数量限制
ORDER BY：对结果中的列进行排序，可升序(asc)或降序(desc)
GROUP BY：对结果中的列相同数据分组
DISTINCT关键字：记录去重
2 子句执行顺序
（1）sql语句的书写顺序：
insert\delete\update\select——>from——>where——>group by——>having——>order by
（2）sql语句的执行顺序：
from——>where——>insert\delete\update\select——>group by——>having——>order by
6.5 常用函数
|
类型 |
函数 |
描述 |
|
数学函数 |
RAND() |
随机数函数rand不能传参，产生的0到1之间的浮点数，可以通过参数(种子)使随机数的结果不变，每个种子产生的随机数序列是不同的。 |
|
ROUND(X,Y) |
四舍五入 | |
|
聚合函数 |
AVG() |
返回参数的平均值 |
|
COUNT() |
返回行数计数 | |
|
MAX() |
返回最大值 | |
|
MIN() |
返回最小值 | |
|
SUM() |
返回总和 | |
|
字符串函数 |
CONCAT(s1,s2) |
拼接字符串，和null 拼接得null |
|
LENGTH(str) |
字符串长度 | |
|
REVERSE(str) |
字符串颠倒 | |
|
LOWER(str), UPPER(str) |
大写转小写，小写转大写 | |
|
日期与时间函数 |
NOW() |
返回当前日期和时间 |
|
CURDATE()或CURRENT_DATE() |
返回当前日期 | |
|
CURTIME()或CURRENT_TIME() |
返回当前时间 | |
|
DATE_FORMAT(date,fmt) |
格式化输出当前日期和时间 # 参考文档 https://www.w3school.com.cn/sql/func_date_format.asp | |
|
DATE_SUB(date,INTERVAL int keyword) |
返回date加上间隔时间int的结果 # 返回从现在时间减去2天的日期和时间 select date_sub(now(),INTERVAL 2 DAY); | |
|
系统信息函数 |
DATABASE() |
返回当前数据库名 |
|
FOUND_ROWS() |
返回最后一个select查询检索的总行数 | |
|
USER()或current_user() |
返回当前登录用户 | |
|
VERSION() |
返回数据库版本 |
7 数据库备份与恢复
7.1 备份类型
|
备份分类 |
|
描述 |
备份工具 |
|
备份方式 |
热备份 |
读写都不受影响 |
mysqldump支持innodb热备 xtrabackup |
|
温备份 |
只能读，不能写 |
mysqldump支持myisam温备 | |
|
冷备份 |
服务脱机备份，不能读写，保证数据的完整性 |
mysqlhotcopy | |
|
备份特性分为 |
完整备份 |
备份所有数据 |
mysqldump xtrabackup |
|
增量备份 |
从一个时间点到另一个时间点所有更改的数据，仅备份上次完整备份或增量备份以来变化的数据。 恢复：完备+所有的增量备份 |
mysqldump+binglog xtrabackup | |
|
差异备份 |
仅备份上次完整备份以来变化的数据。 恢复：完备+任意一次的差异备份 |
| |
|
备份类型 |
物理备份 |
拷贝原始数据目录和其它文件; 比逻辑备份速度快； 适合备份出现问题时快速恢复的大型数据库。 |
cp/scp/tar/rsync/xtrabackup |
|
逻辑备份 |
(1) 通过查询数据库表结构和内容进行备份(insert、delete、update、create、drop、alter)； (2) 比物理备份速度慢； (3) 可以修改备份文件； (4) 格式简单，数据损坏几率小； (5) 所有存储引擎都可以； (6) 适合数据量较少的数据库。 |
mysqldump select …… into outfile; | |
|
快照备份 |
基于文件系统实现，给定时间点的逻辑副本，而不是整个文件系统的物理副本，是一种热备份方式，物理拷贝。 |
LVM、ZFS |
7.2 mysqldump备份
1 常用选项
|
选项 |
描述 |
|
-h, --host=name |
主机名 |
|
-u, --user=name |
用户名 |
|
-P, --port=# |
端口 |
|
-p, --password[=name] |
密码 |
|
-A, --all-databases |
导出所有数据库 |
|
-B, --databases |
指定导出的数据库名，多个库时用空格分隔 |
|
--add-locks |
默认on，在insert语句之前添加LOCK TABLES并且之后UNLOCK TABLE。使用--skip-add-locks关闭。 |
|
-x, --lock-all-tables |
默认off，锁定所有表。全局锁，并自动关闭 --single-transcation和--lock-tables选项 |
|
-l, --lock-tables |
默认on，只锁导出时的表 |
|
-f, --force |
忽略SQL错误，默认关闭 |
|
--master-data |
追加binlog日志位置和文件名到输出文件中。如果等于1，将输出CHANAGE MASTER命令，如果等于2，输出CHANAGE MASTER命令前面加注释。该选项会自动关闭 --lock-tables和 --single-transcation选项。 |
|
-d, --no-data |
只导出表结构，多个表用空格分隔 |
|
-R, --routines |
导出函数和存储过程 |
|
--triggers |
导出触发器 |
|
--single-transaction |
设置事务隔离状态并使用一致性快照开始事务，再unlock tables，备份期间不影响事务操作，只导出快照的时间点数据，适于innodb，与 --lock-tables选项互斥。 |
|
--set-gtid-purged[=name] |
添加SET @@GLOBAL.GTID_PURGED到输出，默认AUTO，其他可选OFF和ON，AUTO对于启用GTID的会输出，没有启用的不输出。 |
|
-q, --quick |
不缓冲查询，直接转储，默认开启。--skip-quick关闭 |
|
-F, --flush-logs |
刷新日志 |
2 完整备份与恢复
(1) 备份所有数据库
1) 备份命令
# mysqldump -uroot -p'mysql5.7@2020' -A -R --single-transaction --events --hex-blob --triggers --flush-privileges > /tmp/dump.sql
2) 主从复制时全备主库的命令
# mysqldump -uroot -p'Liuchang@2020' -A -F -R --single-transaction --master-data=1 --events --hex-blob --triggers --flush-privileges | gzip > /tmp/mysql_master_20200821.sql.bak.gz
# 普通备份参数：
# -R: 转储存储的例程（功能和过程）；
# -E: --events：转储事件；
# -A: 转储所有数据库, 这将与--databases以及所有已选择的数据库相同；
# -B: 转储多个数据库，增加建库语句(如果库不存在)和use连接库的语句；
# --hex-blob: 转储十六进制格式的二进制字符串（BINARY，VARBINARY，BLOB）；
# --triggers: 为每个转储的表转储触发器；
# --flush-privileges: 转储mysql数据库后，发出FLUSH PRIVILEGES语句;
# --single-transaction: 设置事务的隔离级别为可重复读(REPEATABLE READ)，用于热备，只适用于MySQL InnoDB引擎。
# 用于MySQL开启binlog时的参数:
# -F: 开始转储之前刷新服务器中的日志文件；
# --master-data=1: 备份中增加binlog日志文件名及对应的位置点，1不加注释，2加注释；
# -d：没有行信息，只备份表结构，不备份表数据; Usage: -d <数据库> <表名称，多个表名称可用空格隔开>
# -t：不要写表创建信息，只备份表数据，不备份表结构；Usage: -t <数据库> <表名称，多个表名称可用空格隔开>
(2) 备份指定数据库
# mysqldump -uroot -p'mysql5.7@2020' -B test -R --single-transaction --events --hex-blob --triggers --flush-privileges > /tmp/test.sql
(3) 备份恢复
1) 恢复全库
# mysql -uroot -p"mysql5.7@2020" < /tmp/dump.sql
或
# mysql> source /tmp/dump.sql;
2) 恢复指定库
# mysql -uroot -p"mysql5.7@2020" < /tmp/test.sql
或
# mysql> source /tmp/test.sql;
# mysql> create database test;
# mysql> exit;
# mysql -uroot -p"mysql5.7@2020" test < /tmp/test.sql
或
# mysql> create database test;
# use test;
# mysql> source /tmp/test.sql;
3 增量备份与恢复(基于二进制日志实现增量备份)
(1) 前提必须开启二进制日志
# vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = /usr/local/mysql/data/mysql-bin
binlog_cache_size = 4M
binlog_format = mixed
max_binlog_cache_size = 8M
max_binlog_size = 512M
expire_logs_days = 14
# systemctl restart mysqld
(2) 创建实验库和表
mysql> create database test;
mysql> use test;
mysql> create table user(username varchar(50));
(3) 完整备份test库
# ls -l /tmp/
-rw-r--r-- 1 root root 2143 9月 19 23:01 2020-09-19_23:01:37-test.sql
(4) 往user表中增加数据
mysql> insert into user(username) values ("zhangsan"),("lisi");
mysql> select * from user;
(5) 删除test数据库
mysql> drop database test;
(6) 停止数据库对外提供访问
1) 如果防火墙没有开启
# systemctl start firewalld
2) 如果防火墙开启
# firewall-cmd --zone=public --remove-port=3306/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all
(7) 将内存中binlog日志刷新到磁盘
# mysql> flush logs;
# mysql> show binary logs;
(8) 关闭binlog日志(防止还原备份或binlog日志时，sql语句再次写入到binlog日志中)
将/etc/my.cnf中的log-bin配置参数注释掉
# vim /etc/my.cnf
# log-bin = /usr/local/mysql/data/mysql-bin
# systemctl restart mysqld
(9) 恢复完整备份
# mysql -uroot -p"mysql5.7@2020" < /tmp/2020-09-19_23\:01\:37-test.sql
# mysql> use test;
# mysql> select * from user;
Empty set (0.00 sec)
(10) 基于二进制日志恢复
备库，删库，根据备库找到库的备份点，再还原binlog日志。
1) 确定二进制日志文件的名称
# head -30 /tmp/2020-09-19_23\:01\:37-test.sql
2) 检查binlog日志文件确定恢复日志位置，可以将接近的时间段日志重定向到文本文件中查看
# mysqlbinlog --no-defaults -v --base64-output=decode-rows \
--start-datetime="2020-09-19 23:01:37" \
/usr/local/mysql/data/mysql-bin.000002 \
> /tmp/mysql-bin-000002.txt
# 参数说明
--no-defaults：解决"mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'"报错。
-v: 显示sql语句。
--base64-output=decode-rows：把基于行的事件解码成一个SQL语句。
-d test：只显示指定数据库的binlog日志。
# cat /tmp/mysql-bin-000002.txt
3) 恢复
基于时间点恢复
--stop-datetime="2020-09-19 23:04:33" \
-d test \
/usr/local/mysql/data/mysql-bin.000002 \
| mysql -uroot -p"mysql5.7@2020"
或 基于位置点恢复
# mysqlbinlog --no-defaults \
-d test \
/usr/local/mysql/data/mysql-bin.000002 \
| mysql -uroot -p"mysql5.7@2020"
查看数据
# mysql> select * from user;
说明：如果binlog日志中"drop database test"这条sql语句下还有其它库的sql语句，也需要进行恢复binlog的操作，如下，如果有多个binlog日志，也同理。
# mysqlbinlog --no-defaults \
--start-datetime="2020-09-19 23:05:35" \
-d test \
/usr/local/mysql/data/mysql-bin.000002 \
| mysql -uroot -p"mysql5.7@2020"
或
# mysqlbinlog --no-defaults \
--start-position=626 \
-d test \
/usr/local/mysql/data/mysql-bin.000002 \
| mysql -uroot -p"mysql5.7@2020"
4) 到此mysql基于二级制日志还原完成，接下来是恢复服务
开启binlog日志
# vim /etc/my.cnf
log-bin = /usr/local/mysql/data/mysql-bin
# systemctl restart mysqld
关闭防火墙或放行防火墙3306端口
# systemctl stop firewalld
或
# firewall-cmd --zone=public --add-port=3306/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all
5) 补充
A 取binlog日志
# --start-position=<start point>
取binlog日志文件中指定位置到末尾位置的binlog日志。
# --stop-position=<stop point>
取binlog日志文件中开始位置到指定位置的binlog日志。
两个参数同时使用：
取binlog日志文件中特定范围内的binlog日志。
两个参数都不用：
取整个binlog日志文件中的binlog日志。
# --start-datetime=<start time>
# --stop-datetime=<stop time>
同理
B binglog一些常用操作
查看数据库所有日志文件
# mysql> show binary logs;
# mysql> show master logs;
查看指定的binlog文件信息
# mysql> show binlog events in 'mysql-bin.000001';
将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件
# mysql> flush logs;
删除所有二进制日志，并重新(mysql-bin.000001)开始记录
# mysql> reset master;
7.3 xtrabackup备份
|
选项 |
描述 |
|
-u, --user=name |
用户 |
|
-p, --password=name |
密码 |
|
-H, --host=name |
主机名 |
|
-P, --port=# |
端口 |
|
--socket=# |
指定socket文件路径 |
|
--databases=name |
备份数据库，多个以空格分隔，默认备份所有库 |
|
--defaults-file=# |
指定my.cnf配置文件 |
|
--apply-log |
日志回滚 |
|
--incremental=# |
增量备份，后跟增量备份路径 |
|
--incremental-basedir=# |
增量备份，指上次增量备份路径 |
|
--redo-only |
合并完全备份和增量备份文件 |
|
--copy-back |
将备份数据恢复到数据库目录，数据库目录要为空 |
|
--no-timestramp |
生成备份文件不以时间戳为目录名 |
|
--stream=# |
流的格式做备份，将备份归档，--stream-tar |
|
--remote-host=user@ip DST_DIR |
备份到远程主机 |
2 xtrabackup安装
用mysqldump备份 100G+ 的数据库，再加上服务器繁忙，备份速度像蜗牛似的，xtrabackup比较适合备份大的数据库，而且备份效率也高。
(1) xtrabackup 官方下载地址：
https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
(2) 安装
# yum install percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm -y
# xtrabackup -v
3 完整备份与恢复
# innobackupex -Hlocalhost -P3306 -uroot -p"mysql5.7@2020" /tmp/mysql_backup/
说明：备份目录/tmp/mysql_backup/如果不存在会自动创建
# ls -l /tmp/mysql_backup/
总用量 0
drwxr-x--- 6 root root 205 9月 20 01:15 2020-09-20_01-15-18
备份文件说明
# ls -l /tmp/mysql_backup/2020-09-20_01-15-18/
backup-my.cnf：记录innobackup使用到的mysql参数。
xtrabackup_checkpoints：记录备份的类型、开始、结束的日志序列号。
xtrabackup_logfile：备份中会开启一个log copy线程，用来监控innodb日志文件(ib_logfile)，如果修改就会复制到这个文件。
(2) 备份恢复
1) 停止数据库
# systemctl stop mysqld
2) 删除mysql数据目录
# rm -rf /usr/local/mysql/data/
说明：还原mysql数据目录/usr/local/mysql/data/如果不存在会自动创建。
3) 还原数据库数据目录
回滚日志
# innobackupex --apply-log --redo-only /tmp/mysql_backup/2020-09-20_01-15-18/
还原完整库
# innobackupex --defaults-file=/etc/my.cnf --copy-back /tmp/mysql_backup/2020-09-20_01-15-18/
4) 赋予mysql数据目录mysql权限
# chown -R mysql.mysql /usr/local/mysql/data/
5) 启动mysql
# systemctl start mysqld
4 增量备份与恢复
(1) 增量备份
1) 完整备份
# innobackupex -uroot -p"mysql5.7@2020" /mysql_backup/
完整备份目录
# ls /mysql_backup/2020-09-20_02-33-50/
2) 增量备份一
上次备份目录为/mysql_backup/2020-09-20_02-33-50/
第一次增量备份目录
# ls /mysql_backup/2020-09-20_02-47-43/
3) 增量备份二
上次备份目录为/mysql_backup/2020-09-20_02-47-43/
# innobackupex -uroot -p"mysql5.7@2020" --incremental /mysql_backup/ --incremental-basedir=/mysql_backup/2020-09-20_02-47-43/
第二次增量备份目录
# ls /mysql_backup/2020-09-20_02-52-08/
4) 查看xtrabackup_checkpoints文件，可以看到根据日志序号来增量备份
# du -sh ./*
(2) 备份恢复
备份恢复思路：
将增量备份一、增量备份二、合并到完整备份，加到一起出来一个新的完整备份，将新的完整备份以拷贝的形式到数据库空目录。
1) 日志回滚
xtrabackup把备份过程中可能有尚未提交的事务或已经提交但未同步数据文件的事务，写到xtrabackup_logfile文件，所以要先通过这个日志文件回滚，把未完成的事务同步到备份文件，保证数据文件处于一致性。
# innobackupex --apply-log --redo-only /mysql_backup/2020-09-20_02-33-50/
# cat /mysql_backup/2020-09-20_02-33-50/xtrabackup_checkpoints
# innobackupex --apply-log --redo-only /mysql_backup/2020-09-20_02-33-50/ --incremental-dir=/mysql_backup/2020-09-20_02-47-43/
# cat /mysql_backup/2020-09-20_02-33-50/xtrabackup_checkpoints
3) 合并第二个增量备份
# innobackupex --apply-log --redo-only /mysql_backup/2020-09-20_02-33-50/ --incremental-dir=/mysql_backup/2020-09-20_02-52-08/
# cat /mysql_backup/2020-09-20_02-33-50/xtrabackup_checkpoints
4) 停止mysql服务并删除mysql数据目录
# systemctl stop mysqld
# rm -rf /usr/local/mysql/data/
5) 恢复完整备份
# innobackupex --defaults-file=/etc/my.cnf --copy-back /mysql_backup/2020-09-20_02-33-50/
6) 修改mysql数据目录权限
# chown -R mysql.mysql /usr/local/mysql/data/
7) 启动mysql服务
# systemctl start mysqld
5 备份文件压缩归档
在slavenode3：172.16.1.123(mysql服务器)和slavenode4：172.16.1.124(备份服务器)上都要创建
# mkdir -p /mysql_backup/
(1) 归档并发送到备份服务器
# innobackupex --user=root --password="mysql5.7@2020" --stream=tar /mysql_backup/ 2>/mysql_backup/`date +%F_%H-%M-%S`.log |ssh root@172.16.1.124 "cat - > /mysql_backup/`date +%F_%H-%M-%S`.tar"
[root@slavenode3 ~]# ls /mysql_backup/
2020-09-20_22-23-38.log
2020-09-20_22-23-38.tar
[root@slavenode3 data1]# tar -ixvf /mysql_backup/2020-09-20_22-23-38.tar
(2) 归档备份
# innobackupex -uroot -p"mysql5.7@2020" --stream=tar /mysql_backup/ > /mysql_backup/`date +%F_%H-%M-%S`.tar 2>/dev/null
[root@slavenode3 ~]# ls /mysql_backup/ -l
-rw-r--r-- 1 root root 26304000 9月 20 22:27 2020-09-20_22-27-55.tar
[root@slavenode3 data2]# tar -ixvf /mysql_backup/2020-09-20_22-27-55.tar
(3) 压缩归档备份
# innobackupex -uroot -p"mysql5.7@2020" --stream=tar /mysql_backup/ 2>/dev/null |gzip >/mysql_backup/`date +%F_%H-%M-%S`.tar.gz
[root@slavenode3 ~]# ls /mysql_backup/ -l
-rw-r--r-- 1 root root 612406 9月 20 22:32 2020-09-20_22-32-35.tar.gz
创建/data3/目录并进入目录后解压
[root@slavenode3 data3]# tar -izxvf /mysql_backup/2020-09-20_22-32-35.tar.gz
6 Xtrabackup备份原理
(1) 物理备份，不锁表，那么怎么保证现有的数据(备份过程中有尚未提交或已经提交但未同步事务)与复制出来的数据一致呢。
(2) 我们知道MySQL修改操作都会先记录在ib_logfile日志文件，再同步到磁盘，这个文件并重复使用。
(3) xtrabackup在复制期间会开启一个线程用来监控ib_logfile日志文件，如果有修改就从上次记录的日志序列号(checkpoins)开始复制新增内容到Logfile文件。复制结束后，把logfile事务日志进行回滚，把未完成的事务同步到ibdata1和ibd里面，来保证数据一致性。这与Mysql崩溃后恢复基本操作一样。
8 补充
8.1 mysql 配置
(1) mysql my.cnf配置文件中1、on、ON都是表示参数项开启，0、off、OFF都是表示参数项关闭。
(2) mysql my.cnf配置文件中 = 紧贴value值或不紧贴都可。
(3) mysqld_safe启动mysql会启动一个mysqld_safe守护进程来监控mysqld进程，如果mysqld进程死掉，mysqld_safe守护进程会重新启动mysqld进程。mysqld_safte启动mysql 会读取my.cnf中的[mysqld] [mysqld_safte]作用域。mysqld启动mysql会读取my.cnf 中的[mysqld]作用域。
8.2 数据库编码
客户端的编码格式需要和数据库的编码格式保持一致，否则会导致乱码的出现。
可以理解数据库、表的编码格式是一种编码容器，在向该容器写入数据时以容器编码格式进行编码，改变容器的编码格式并不能改变已存储数据的编码格式。
(1) my.cnf中配置数据库编码：
创建数据库时如果不指定编码则继承该配置；在库中创建表时如果不指定表的编码格式，则继承库的编码格式。
(2) my.cnf中配置客户端编码：
1) 在mysql服务器终端上向数据库写入数据时，如果没有配置该配置，则使用服务器的编码，如果配置了该配置，则使用配置的编码。
2) 使用其它软件连接数据库时使用接软件的编码配置。
