mysql进阶

mysql进阶

1.mysql二进制安装

# 关闭防火墙和selinux
[root@96 ~]# systemctl stop firewalld
[root@96 ~]# systemctl disable firewalld
[root@96 ~]# getenforce
Disabled


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


# 下载二进制格式的mysql软件包
[root@96 ~]# cd /usr/src/
[root@96 src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

# 解压软件至/usr/local/
[root@96 src]# ls
debug  kernels  mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
[root@96 src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@96 ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.22-linux-glibc2.12-x86_64  share
[root@96 ~]# cd /usr/local/
[root@96 local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.22-linux-glibc2.12-x86_64/’

# 修改目录/usr/local/mysql的属主属组
[root@96 ~]# chown -R mysql.mysql /usr/local/mysql

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


# 建立数据存放目录
[root@96 mysql]# mkdir /opt/data
[root@96 mysql]# chown -R mysql.mysql /opt/data/
[root@96 mysql]# ll /opt/
total 0
drwxr-xr-x 2 mysql mysql 6 Aug 14 16:54 data

# 初始化数据库
[root@96 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2018-08-15T07:57:46.168380Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-15T07:57:50.542516Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-15T07:57:50.927286Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-15T07:57:51.071260Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e8600890-a060-11e8-b1a2-000c294c50b4.
2018-08-15T07:57:51.074566Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-15T07:57:51.078089Z 1 [Note] A temporary password is generatedfor root@localhost: hfGdwnViq6,,
# 请注意，这个命令的最后会生成一个临时密码，此处密码是hfGdwnViq6,,

# 配置mysql
[root@96 ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
‘/usr/local/include/mysql’ -> ‘/usr/local/mysql/include/’
[root@96 ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@96 ~]# ldconfig -v

# 生成配置文件
[root@96 ~]# cat > /etc/my.cnf <<EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
EOF

# 配置服务启动脚本
[root@96 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@96 ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@96 ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

# 启动mysql
[root@96 ~]# service mysqld start
Starting MySQL.. SUCCESS!  
[root@96 ~]# ss -antl
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN      0      128         *:22                      *:*
LISTEN      0      100    127.0.0.1:25                      *:*
LISTEN      0      128        :::22                     :::*
LISTEN      0      100       ::1:25                     :::*
LISTEN      0      80         :::3306                   :::* 
 
# 修改密码
# 使用临时密码登录
[root@96 ~]# mysql -uroot -p‘hfGdwnViq6,,’
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

# 设置新密码
mysql> set password = password('shicailun123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye

# 使用新密码登录
[root@96 ~]# mysql -uroot -p‘shicailun123！’
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2.mysql的配置文件

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

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

mysql常用配置

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

3.mysql备份与恢复

3.1 数据库常用备份方案

数据库备份方案：

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

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

3.2mysql备份与恢复

3.2.1mysql备份工具mysqldump

# 语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
# 常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

3.2.2 备份与恢复展示

# 查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| scl                |
| sys                |
+--------------------+

# 进入scl数据库查看有哪些表
mysql> use scl
mysql> show tables;
+---------------+
| Tables_in_scl |
+---------------+
| student       |
+---------------+

# 查看表的内容
mysql> select *from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   10 |
|  2 | jerry |   20 |
|  3 | aa    |   30 |
|  4 | bb    |   40 |
|  5 | cc    |   50 |
+----+-------+------+

# 备份所有数据库
[root@100 ~]# mysqldump -uroot -pscl666 --all-databases > all_2019908182018.sql
[root@100 ~]# ls
all_2019908182018.sql




# 备份scl这一个数据库
[root@100 ~]# mysqldump -uroot -pscl666 --databases scl > scl_201908182020.sql
[root@100 ~]# ls
scl_201908182020.sql

# 删除scl这个数据库
mysql> drop database scl;
Query OK, 1 row affected (0.05 sec)

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

# 恢复scl数据库
[root@100 ~]# mysql -uroot -pscl666 < scl_201908182020.sql 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| scl                |
| sys                |
+--------------------+
mysql> select *from scl.student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   10 |
|  2 | jerry |   20 |
|  3 | aa    |   30 |
|  4 | bb    |   40 |
|  5 | cc    |   50 |
+----+-------+------+





# 备份scl数据库里面的student这张表（多张表直接空格隔开）
[root@100 ~]# mysqldump -uroot -pscl666 scl student > table_student_201902182026.sql
[root@100 ~]# ls
table_student_201902182026.sql

# 删除scl数据库中student这张表
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> 

# 恢复student
mysql> source table_student_201902182026.sql;		# 需要进入scl数据库才能使用
mysql> show tables;
+---------------+
| Tables_in_scl |
+---------------+
| student       |
+---------------+
或者   \
[root@100 ~]# mysql -uroot -pscl666 scl < table_student_201902182026.sql
mysql> show tables;
+---------------+
| Tables_in_scl |
+---------------+
| student       |
+---------------+
mysql> select *from scl.student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   10 |
|  2 | jerry |   20 |
|  3 | aa    |   30 |
|  4 | bb    |   40 |
|  5 | cc    |   50 |
+----+-------+------+

3.2.3 mysql差异备份

开启MySQL服务器的二进制日志功能
编辑配置文件/etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id=1          # 添加此行，设置服务器标识符
log-bin=mysql_bin    # 添加此行，开启二进制日志功能

# 重启服务
[root@100 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!

# 全量备份数据库
[root@100 ~]# mysqldump -uroot -pscl666 --all-databases > all_201908182102.sql
[root@100 ~]# ls
all_201908182102.sql

# 往scl数据库里面student表中新增内容
mysql> use scl;
mysql> select *from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   10 |
|  2 | jerry |   20 |
|  3 | aa    |   30 |
|  4 | bb    |   40 |
|  5 | cc    |   50 |
+----+-------+------+
mysql> insert student (id,name,age) values (6,'zhangshan',13),(7,'lisi',23);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   10 |
|  2 | jerry     |   20 |
|  3 | aa        |   30 |
|  4 | bb        |   40 |
|  5 | cc        |   50 |
|  6 | zhangshan |   13 |
|  7 | lisi      |   23 |
+----+-----------+------+

# 修改student表中的内容
mysql> update student sedent set age = 100 where name = 'tom';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from student;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |  100 |
|  2 | jerry     |   20 |
|  3 | aa        |   30 |
|  4 | bb        |   40 |
|  5 | cc        |   50 |
|  6 | zhangshan |   13 |
|  7 | lisi      |   23 |
+----+-----------+------+

# 删除数据库scl
mysql> drop database scl;
Query OK, 1 row affected (0.06 sec)

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

# 刷新创建新的二进制日志（发生问题后建议立马刷新）
[root@100 ~]# mysqladmin -uroot -pscl666 flush-logs
[root@100 ~]# ll /opt/data/
总用量 122952
-rw-r----- 1 mysql mysql     4113 8月  18 21:01 100.err
-rw-r----- 1 mysql mysql       56 8月  18 15:48 auto.cnf
-rw-r----- 1 mysql mysql     2131 8月  18 21:01 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  18 21:13 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  18 21:13 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月  18 15:48 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月  18 21:02 ibtmp1
-rw-r----- 1 mysql mysql     8818 8月  18 21:01 localhost.localdomain.err
drwxr-x--- 2 mysql mysql     4096 8月  18 17:17 mysql
-rw-r----- 1 mysql mysql      913 8月  18 21:16 mysql_bin.000001
-rw-r----- 1 mysql mysql      154 8月  18 21:16 mysql_bin.000002		# 这就是生成的新的二进制日志文件

恢复全量备份

[root@100 ~]# mysql -uroot -pscl666 < all_201908182102.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| scl                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select *from scl.student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | tom   |   10 |
|  2 | jerry |   20 |
|  3 | aa    |   30 |
|  4 | bb    |   40 |
|  5 | cc    |   50 |
+----+-------+------+
# 数据库和表都恢复了，但是我们做的修改和新增的操作还没有恢复，所以需要恢复差异备份

恢复差异备份

[root@100 ~]# ll /opt/data/
总用量 122952
-rw-r----- 1 mysql mysql     4113 8月  18 21:01 100.err
-rw-r----- 1 mysql mysql       56 8月  18 15:48 auto.cnf
-rw-r----- 1 mysql mysql     2131 8月  18 21:01 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  18 21:13 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  18 21:13 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月  18 15:48 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月  18 21:02 ibtmp1
-rw-r----- 1 mysql mysql     8818 8月  18 21:01 localhost.localdomain.err
drwxr-x--- 2 mysql mysql     4096 8月  18 17:17 mysql
-rw-r----- 1 mysql mysql      913 8月  18 21:16 mysql_bin.000001
-rw-r----- 1 mysql mysql      154 8月  18 21:16 mysql_bin.000002

# 检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql_bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 219 | Query          |         1 |         290 | BEGIN                                 |
| mysql_bin.000001 | 290 | Table_map      |         1 |         343 | table_id: 118 (scl.student)           |
| mysql_bin.000001 | 343 | Write_rows     |         1 |         405 | table_id: 118 flags: STMT_END_F       |
| mysql_bin.000001 | 405 | Xid            |         1 |         436 | COMMIT /* xid=416 */                  |
| mysql_bin.000001 | 436 | Anonymous_Gtid |         1 |         501 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 501 | Query          |         1 |         572 | BEGIN                                 |
| mysql_bin.000001 | 572 | Table_map      |         1 |         625 | table_id: 118 (scl.student)           |
| mysql_bin.000001 | 625 | Update_rows    |         1 |         681 | table_id: 118 flags: STMT_END_F       |
| mysql_bin.000001 | 681 | Xid            |         1 |         712 | COMMIT /* xid=419 */                  |
| mysql_bin.000001 | 712 | Anonymous_Gtid |         1 |         777 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 777 | Query          |         1 |         866 | drop database scl                     |
| mysql_bin.000001 | 866 | Rotate         |         1 |         913 | mysql_bin.000002;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+



# 使用mysqlbinlog恢复差异备份
[root@100 ~]# mysqlbinlog --stop-position=777 /opt/data/mysql_bin.000001 |mysql -uroot -pscl666
[root@100 ~]# mysql -uroot -pscl666 -e 'select *from scl.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |  100 |
|  2 | jerry     |   20 |
|  3 | aa        |   30 |
|  4 | bb        |   40 |
|  5 | cc        |   50 |
|  6 | zhangshan |   13 |
|  7 | lisi      |   23 |
+----+-----------+------+
# 到目前为止才真正恢复了