Mysql进阶

原创

你的阿万 2020-05-06 15:54:14 ©著作权

文章标签 Mysql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者你的阿万的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.软件包二进制下载

//下载二进制格式的Mysql软件包
[root@wlw ~]# cd /usr/src/
[root@wlw src]# rz

[root@wlw src]# ls       mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz
//创建用户和组
[root@wlw src]# groupadd -r mysql
[root@wlw src]# useradd -M -s /sbin/nologin  -g mysql mysql

//解压软件至/usr/local
[root@wlw src]# tar xf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@wlw src]# cd /usr/local/
[root@wlw local]# ls
apache  apr-util  etc    include  lib64    mysql-5.7.25-linux-glibc2.12-x86_64  share
apr     bin       games  lib      libexec  sbin                                 src
[root@wlw local]# ln -sv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql 

'mysql' -> 'mysql-5.7.25-linux-glibc2.12-x86_64/'
[root@wlw local]# ll
总用量 0
drwxr-xr-x. 13 root root 152 2月  19 13:00 apache
drwxr-xr-x.  6 root root  58 2月  19 12:47 apr
drwxr-xr-x.  5 root root  43 2月  19 12:49 apr-util
drwxr-xr-x.  2 root root   6 3月  10 2016 bin
drwxr-xr-x.  2 root root   6 3月  10 2016 etc
drwxr-xr-x.  2 root root   6 3月  10 2016 games
drwxr-xr-x.  2 root root   6 3月  10 2016 include
drwxr-xr-x.  2 root root   6 3月  10 2016 lib
drwxr-xr-x.  2 root root   6 3月  10 2016 lib64
drwxr-xr-x.  2 root root   6 3月  10 2016 libexec
lrwxrwxrwx.  1 root root  36 4月  21 15:29 mysql -> mysql-5.7.25-linux-glibc2.12-x86_64/
drwxr-xr-x.  9 root root 129 4月  21 15:00 mysql-5.7.25-linux-glibc2.12-x86_64
drwxr-xr-x.  2 root root   6 3月  10 2016 sbin
drwxr-xr-x.  5 root root  49 2月  19 18:42 share
drwxr-xr-x.  2 root root   6 3月  10 2016 src

//修改/usr/local/mysql
[root@wlw ~]# chown -R mysql.mysql /usr/local/mysql
[root@wlw ~]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 4月  21 15:29 /usr/local/mysql -> mysql-5.7.25-linux-glibc2.12-x86_64/

//配置环境变量
[root@wlw ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@wlw local]# source /etc/profile.d/mysql.sh
[root@wlw ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root@wlw ~]# chown -R mysql.mysql /opt/data
[root@wan opt]# mysqld --initialize --datadir=/opt/data/ --user=mysql &> /root/pass
[root@wan ~]# cat pass
[root@wan ~]# cat pass | awk 'NR==6{print$NF}'
FkrXaVhX&8Pr

//生成配置文件
[root@wlw ~]# 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@wlw ~]# cat /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

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

//启动服务
[root@wlw ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/wlw.err'.
 SUCCESS! 
 //使用临时密码登录mysql
[root@wlw ~]# mysql -uroot -pswvpK5-KCNCe

//修改密码
mysql> set password = password('w');
Query OK, 0 rows affected, 1 warning (0.00 sec)
//刷新一下
mysql> flush privilrges；

2.mysql配置文件

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

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FIL
E --> ~/.my.cnf
//配置免密登录
[root@wlw ~]# vim .my.cnf
[root@wlw ~]# cat .my.cnf 
[client]
user=root
password=w
[root@wlw ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> exit
Bye

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将无法正常处理连接请求

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    (默认的一般为3306)
 
备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wenhs              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use wenhs;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_wenhs |
+-----------------+
| student         |
| teacher         |
+-----------------+
2 rows in set (0.00 sec)

mysql> exit
Bye

3.3 mysql数据恢复

恢复wlw数据库
[root@wlw ~]# ls
all-2020-04-25_15:12:03.sql              模板
[root@wlw ~]# mysql  <all-2020-04-25_15:12:03.sql	配置了免密登录
[root@wlw ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wlw              |
+--------------------+

恢复wlw数据库的student表和teacher表
mysql> use wlw;
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 table-2020-04-25_15:14:03.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)

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

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

恢复整个数据库
[root@wlw ~]# mysql < all-2020-04-25_15:12:03.sql 
[root@wlw ~]# mysql wlw < table-2020-04-25_15:14:03.sql    此步多余,第一次全备中,库里面数据都已经备份,不需要这步
[root@wlw ~]# mysql -e 'select * from wlw.student;'
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

3.4差异恢复与备份

开启mysql服务器的二进制日志功能

[root@wlw ~]# vim /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@wlw ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@wlw ~]# mysql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wlw                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from wlw;
+---------------+
| Tables_in_wlw |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from wlw.student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | wlw  |   19 |
|  2 | lll  |   20 |
|  3 | www  |   21 |
+----+------+------+
3 rows in set (0.03 sec)

//对数据库进行完全备份
[root@wlw ~]# mysqldump -uroot -pw --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20200423.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wlw ~]# ll
总用量 3000
-rw-r--r--. 1 root root 3052667 12月 19 12:04 1.jpg
-rw-r--r--. 1 root root       0 4月  23 16:36 all-20200423.sql
-rw-------. 1 root root    1451 2月  19 18:47 anaconda-ks.cfg
-rw-r--r--. 1 root root     906 2月  20 10:15 anaconda-ks.cfg.tar.zip
-rw-r--r--. 1 root root      18 2月  19 13:09 index.html
-rw-r--r--. 1 root root     802 4月  23 09:35 pass
drwxr-xr-x. 2 root root     136 2月  19 13:14 youxi

//增加新的内容
[root@wlw ~]# mysql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wlw                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use wlw;
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> show tables;
+---------------+
| Tables_in_wlw |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> insert into student values(4,'hehe',20),(5,'xixi',50);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | wlw  |   19 |
|  2 | lll  |   20 |
|  3 | www  |   21 |
|  4 | hehe |   20 |
|  5 | xixi |   50 |
+----+------+------+
5 rows in set (0.01 sec)
//改年龄
mysql> update student set age = 40 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | wlw  |   19 |
|  2 | lll  |   20 |
|  3 | www  |   21 |
|  4 | hehe |   40 |
|  5 | xixi |   50 |
+----+------+------+
5 rows in set (0.00 sec)
mysql差异备份恢复 模拟误删数据

[root@wlw ~]# mysql -e 'drop database wlw;'
[root@wlw ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
//这里看到数据库已经被删除
刷新创建新的二进制日志

[root@wlw ~]# ll /opt/data
总用量 122944
-rw-r-----. 1 mysql mysql       56 4月  23 09:35 auto.cnf
-rw-r-----. 1 mysql mysql      370 4月  23 16:32 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 4月  23 16:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 4月  23 16:45 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 4月  23 09:35 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 4月  23 16:36 ibtmp1
drwxr-x---. 2 mysql mysql     4096 4月  23 09:35 mysql
-rw-r-----. 1 mysql mysql      863 4月  23 16:45 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 4月  23 16:36 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 4月  23 16:32 mysql.pid
drwxr-x---. 2 mysql mysql     8192 4月  23 09:35 performance_schema
drwxr-x---. 2 mysql mysql     8192 4月  23 09:35 sys
-rw-r-----. 1 mysql mysql    12848 4月  23 16:36 wlw.err
[root@wlw ~]# mysqladmin -uroot -pw flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@wlw ~]# ll /opt/data
总用量 122948
-rw-r-----. 1 mysql mysql       56 4月  23 09:35 auto.cnf
-rw-r-----. 1 mysql mysql      370 4月  23 16:32 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 4月  23 16:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 4月  23 16:45 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 4月  23 09:35 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 4月  23 16:36 ibtmp1
drwxr-x---. 2 mysql mysql     4096 4月  23 09:35 mysql
-rw-r-----. 1 mysql mysql      910 4月  23 16:49 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 4月  23 16:49 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 4月  23 16:49 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 4月  23 16:32 mysql.pid
drwxr-x---. 2 mysql mysql     8192 4月  23 09:35 performance_schema
drwxr-x---. 2 mysql mysql     8192 4月  23 09:35 sys
-rw-r-----. 1 mysql mysql    12916 4月  23 16:47 wlw.err

恢复完全备份

[root@wlw ~]# mysql < all-20200423.sql 
[root@wlw ~]# mysql -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wlw                |
+--------------------+