mysql进阶

1. 二进制格式mysql安装

#wget下载二进制格式的mysql软件包
[root@node01-linux ~]# cd /usr/src/
[root@node01-linux src]# ls
debug  kernels
[root@node01-linux src]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
[root@node01-linux src]# ls
debug  kernels  mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

#创建用户和组
[root@node01-linux ~]# groupadd -r -g 306 mysql
[root@node01-linux ~]# useradd -r -M -s /sbin/nologin -u 306 -g 306 mysql

#解压软件至/usr/local/
[root@node01-linux ~]# ls /usr/local/
bin  etc  games  include  lib  lib64  libexec  sbin  share  src
[root@node01-linux src]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/			//解压
[root@node01-linux ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.30-linux-glibc2.12-x86_64  share

#进/usr/local/ 建立软连接
[root@node01-linux ~]# cd /usr/local/
[root@node01-linux local]# ln -sv mysql-5.7.30-linux-glibc2.12-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.30-linux-glibc2.12-x86_64/’
[root@node01-linux local]# ll
···
lrwxrwxrwx. 1 root root  36 Jun 12 12:06 mysql -> mysql-5.7.30-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jun 12 11:57 mysql-5.7.30-linux-glibc2.12-x86_64
···

#修改目录/usr/local/mysql的属主属组
[root@node01-linux local]# chown -R mysql.mysql mysql*
[root@node01-linux local]# ll
···
lrwxrwxrwx. 1 mysql mysql  36 Jun 12 12:06 mysql -> mysql-5.7.30-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jun 12 11:57 mysql-5.7.30-linux-glibc2.12-x86_64
···

#添加环境变量
[root@node01-linux ~]# ls /usr/local/mysql
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@node01-linux ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH'  > /etc/profile.d/mysql.sh
[root@node01-linux ~]# . /etc/profile.d/mysql.sh 		//读取文件
[root@node01-linux ~]# echo $PATH 
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@node01-linux ~]# which mysql
/usr/local/mysql/bin/mysql

#建立数据存放目录
[root@node01-linux ~]# mkdir /opt/mysqldata
[root@node01-linux ~]# chown -R mysql.mysql /opt/mysqldata/
[root@node01-linux ~]# ll -d /opt/mysqldata/
drwxr-xr-x. 2 mysql mysql 6 Jun 12 16:08 /opt/mysqldata/

#初始化数据库
[root@node01-linux ~]# yum list all|grep aio
[root@node01-linux ~]# yum -y install libaio-devel		//装libaio-devel包

[root@node01-linux ~]# mysqld --initialize --user=mysql --datadir=/opt/mysqldata
···
2020-06-12T08:39:12.553371Z 1 [Note] A temporary password is generated for root@localhost: &dVnnnznT6kZ
#★★★★★注意，这个密码是随机的，一定要记住这个密码，因为一会登录时会用到★★★★★
[root@node01-linux ~]#  echo '&dVnnnznT6kZ' > passwd

#生成配置文件

[root@node01-linux ~]# > /etc/my.cnf	//清空内容
[root@node01-linux ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql		//数据库安装路径
datadir = /opt/mysqldata		//数据存放目录
socket = /tmp/mysql.sock		//套接字文件存放目录
port = 3306						//端口号
pid-file = /opt/mysqldata/mysql.pid		//进程id存放目录
user = mysql							//指定用户
skip-name-resolve						//禁止域名解析（当然，也包括主机名）

#配置服务启动脚本
[root@node01-linux ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[root@node01-linux ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/mysqldata
★★★★★或者★★★★★
[root@node01-linux ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@node01-linux ~]# sed -ri 's#^(datadir=).*#\1/opt/mysqldata#g' /etc/init.d/mysqld

#启动mysql
[root@node01-linux ~]# service mysqld start		//启动
[root@node01-linux ~]# chkconfig mysqld on		//设置开机自启
Starting MySQL.Logging to '/opt/mysqldata/node01-linux.example.com.err'.
 SUCCESS! 
[root@node01-linux ~]# ss -antl
···
LISTEN     0      80      :::3306                :::*

#修改密码，使用临时密码登录
[root@node01-linux ~]# mysql -uroot -p'&dVnnnznT6kZ'
mysql> set password = password('mysql123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit

2. mysql配置文件

mysql 的配置文件为 /etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

#mysql免密、 mysqldump免密
[root@node01-linux ~]# vim .my.cnf 
[client]
user=root
password=mysql123456
[mysqldump]
user=root
password=mysql123456


[root@node01-linux ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求

mysql脚本部署

#!/bin/bash

#yum源
yr=$(ls /etc/yum.repos.d|wc -l)
if [ $yr -eq 0 ];then
    curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
    sed -i 's/$releasever/7/g' /etc/yum.repos.d/CentOS-Base.repo
    sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo
fi

#安装libaio-devel
yum -y install libaio-devel &>/dev/null

#变量
install_dir=/usr/local
soft=mysql-5.7.30-linux-glibc2.12-x86_64
datadir=/opt/mysqldata
read -p "请输入新的数据库密码：" password

#创建用户
id mysql &>/dev/null
if [ $? -ne 0 ];then
    useradd -r -M -s /sbin/nologin -u 306 mysql
fi

#解压
if [ ! -d $install_dir/$soft ];then
    tar xf software/${soft}.tar.gz -C $install_dir
fi

#建立软连接
ln -sv $install_dir/$soft $install_dir/mysql &>/dev/null

#修改属主、属组
chown -R mysql.mysql $install_dir/mysql*

#环境变量
echo "export PATH=$install_dir/mysql/bin:\$PATH"  > /etc/profile.d/mysql.sh

#建立数据存放目录
if [ ! -d $datadir ];then
    mkdir $datadir
fi

#权限
chown -R mysql.mysql $datadir

#初始化数据库、保存临时密码
mdus=$(ls $datadir|wc -l)
if [ $mdus -eq 0 ];then
    $install_dir/mysql/bin/mysqld --initialize --user=mysql --datadir=$datadir &> /tmp/pw_f
    tem_pw=$(grep 'password' /tmp/pw_f|awk '{print $NF}')

#生成配置文件
cat > /etc/my.cnf <<EOF
[mysqld]
basedir = $install_dir/mysql
datadir = $datadir
socket = /tmp/mysql.sock
port = 3306
pid-file = $datadir/mysql.pid
user = mysql
skip-name-resolve
EOF

#配置服务启动脚本
    cp -a $install_dir/mysql/support-files/mysql.server /etc/init.d/mysqld
    sed -ri "s#^(basedir=).*#\1$install_dir/mysql#g" /etc/init.d/mysqld
    sed -ri "s#^(datadir=).*#\1$datadir#g" /etc/init.d/mysqld

fi
chmod +x /etc/init.d/mysqld

#启动mysql、设置开机自启
service mysqld start &>/dev/null
chkconfig mysqld on

#临时密码登录、修改密码
$install_dir/mysql/bin/mysql -uroot -p$tem_pw --connect-expired-password -e "set password=password('$password');" &>/dev/null

$install_dir/mysql/bin/mysql -uroot -p$password -e 'show databases;' &>/dev/null
if [ $? -eq 0 ];then
    echo "您的数据库密码修改成功"
else
    echo "Error your operation fault"
fi

#生成mysql、 mysqldump免密登录配置文件
cat > /root/.my.cnf <<EOF
[client]
user=root
password=$password
[mysqldump]
user=root
password=$password
EOF

3. mysql数据库备份与恢复

---

3.1 数据库常用备份方案

数据库备份方案：

• 全量备份
• 增量备份
• 差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

3.2 mysql备份工具mysqldump

#语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

#常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307


mysql> show databases;	//查看库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables;		//查看表
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

#备份整个数据库(全备)
[root@node01-linux ~]# mysqldump -uroot -pmysql123456 --all-databases > all-20206131130.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@node01-linux ~]# ls
all-20206131130.sql  anaconda-ks.cfg  passwd

#备份school库的teacher表
[root@node01-linux ~]# mysqldump -uroot -pmysql123456 school teacher > teacher-202006131150.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@node01-linux ~]# ls
all-20206131130.sql  anaconda-ks.cfg  passwd  teacher-202006131150.sql

#备份school库里面所有的表
[root@node01-linux ~]# mysqldump school > school-202006131205.sql
[root@node01-linux ~]# ls
all-20206131130.sql  passwd                   teacher-202006131150.sql
anaconda-ks.cfg      school-202006131205.sql

#备份school库
[root@node01-linux ~]# mysqldump --databases school > school-202006131505.sql 
[root@node01-linux ~]# ls
all-20206131130.sql  passwd                   school-202006131505.sql
anaconda-ks.cfg      school-202006131205.sql  teacher-202006131150.sql

#模拟删除school数据库
mysql> drop database school;
Query OK, 2 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3.3 mysql数据恢复

#恢复school数据库
[root@node01-linux ~]# ls
all-20206131130.sql  anaconda-ks.cfg  passwd
[root@node01-linux ~]# mysql < school-202006131505.sql
[root@node01-linux ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

#恢复school数据库里面的teacher表
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> source teacher-202006131150.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

···
#★★★★★或者★★★★★
[root@node01-linux ~]# mysql school < teacher-202006131150.sql

#★★★★★模拟删除整个数据库★★★★★
mysql> drop database school;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#恢复整个数据库
[root@node01-linux ~]# ls
all-20206131130.sql  passwd                   school-202006131505.sql
anaconda-ks.cfg      school-202006131205.sql  teacher-202006131150.sql
[root@node01-linux ~]# mysql < all-20206131130.sql
[root@node01-linux ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

3.4 差异备份与恢复

3.4.1. mysql差异备份

[root@node01-linux ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysqldata/mysql.pid
user = mysql
skip-name-resolve

server-id = 1			//设置服务器标识符
log-bin = mysql.bin		//开启二进制日志功能
[root@node01-linux ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS! 

[root@node01-linux ~]# ls /opt/mysqldata/
mysql.000001	mysql.index

对数据库进行完全备份

[root@node01-linux ~]# mysql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   12 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   20 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   26 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from teacher;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | bj   |   28 |
|  2 | sh   |   33 |
|  3 | gz   |   26 |
|  4 | sz   |   40 |
|  5 | wh   |   20 |
+----+------+------+
5 rows in set (0.00 sec)

#完全备份
[root@node01-linux ~]# mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202006131650.sql

 #增加新内容
[root@node01-linux ~]# mysql
mysql> use school;
mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   12 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   20 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   26 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> insert student(name,age) values('runtime',28);				//新增runtime
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   12 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   20 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   26 |
|  6 | runtime  |   28 |
+----+----------+------+
6 rows in set (0.00 sec)

mysql> update student set age = 50 where name = 'zhangsan';			//修改zhangsan年龄
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;				
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   12 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   50 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   26 |
|  6 | runtime  |   28 |
+----+----------+------+
6 rows in set (0.00 sec)

3.4.2. mysql差异备份恢复

模拟误删数据

mysql> drop database school;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

刷新创建新的二进制日志

[root@node01-linux ~]# ll /opt/mysqldata/
-rw-r-----. 1 mysql mysql      884 Jun 13 17:04 mysql.000002
-rw-r-----. 1 mysql mysql       15 Jun 13 16:52 mysql.index
···
[root@node01-linux ~]# cat /opt/mysqldata/mysql.index 
./mysql.000002

#刷新创建新的二进制日志
[root@node01-linux ~]# mysqladmin -uroot -pmysql123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@node01-linux ~]# ll /opt/mysqldata/
-rw-r-----. 1 mysql mysql      927 Jun 13 17:10 mysql.000002
-rw-r-----. 1 mysql mysql      154 Jun 13 17:10 mysql.000003
-rw-r-----. 1 mysql mysql       30 Jun 13 17:10 mysql.index
···

恢复完全备份

[root@node01-linux ~]# mysql < all-202006131650.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use school; 
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   12 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   20 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   26 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from teacher;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | bj   |   28 |
|  2 | sh   |   33 |
|  3 | gz   |   26 |
|  4 | sz   |   40 |
|  5 | wh   |   20 |
+----+------+------+
5 rows in set (0.00 sec)

恢复差异备份

[root@node01-linux ~]# ll /opt/mysqldata/
-rw-r-----. 1 mysql mysql      927 Jun 13 17:10 mysql.000002
-rw-r-----. 1 mysql mysql   833536 Jun 13 17:13 mysql.000003
···
[root@node01-linux ~]# mysql
mysql> show binlog events in 'mysql.000002';
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name     | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.30-log, Binlog ver: 4 |
| mysql.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql.000002 | 219 | Query          |         1 |         293 | BEGIN                                 |
| mysql.000002 | 293 | Table_map      |         1 |         349 | table_id: 140 (school.student)        |
| mysql.000002 | 349 | Write_rows     |         1 |         398 | table_id: 140 flags: STMT_END_F       |
| mysql.000002 | 398 | Xid            |         1 |         429 | COMMIT /* xid=483 */                  |
| mysql.000002 | 429 | Anonymous_Gtid |         1 |         494 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql.000002 | 494 | Query          |         1 |         568 | BEGIN                                 |
| mysql.000002 | 568 | Table_map      |         1 |         624 | table_id: 140 (school.student)        |
| mysql.000002 | 624 | Update_rows    |         1 |         690 | table_id: 140 flags: STMT_END_F       |
| mysql.000002 | 690 | Xid            |         1 |         721 | COMMIT /* xid=485 */                  |
| mysql.000002 | 721 | Anonymous_Gtid |         1 |         786 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql.000002 | 786 | Query          |         1 |         884 | drop database school                  |
| mysql.000002 | 884 | Rotate         |         1 |         927 | mysql.000003;pos=4                    |
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)

#使用mysqlbinlog恢复差异备份
[root@node01-linux ~]# mysqlbinlog --stop-position=786 /opt/mysqldata/mysql.000002 |mysql -uroot -pmysql123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   12 |
|  2 | jerry    |   16 |
|  3 | zhangsan |   50 |
|  4 | lisi     |   24 |
|  5 | wangwu   |   26 |
|  6 | runtime  |   28 |
+----+----------+------+
6 rows in set (0.00 sec)