mysql进阶

1.mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

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

mysql常用配置文件参数：

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

2.mysql数据库备份

2.1 数据库常用备份方案

数据库备份方案：

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

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

2.2mysql备份工具mysqldump

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

常用的OPTIONS：

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

备份整个数据库（全备）

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)mysql> show tables;+------------------+| Tables_in_linfan |+------------------+| doudou           || linfan           |+------------------+2 rows in set (0.00 sec)[root@linfan ~]# lsanaconda-ks.cfg  doudou.repo  lin  nfs.sh[root@linfan ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-20180818.sqlEnter password:[root@linfan ~]# lsall-20180818.sql  anaconda-ks.cfg  doudou.repo  lin  nfs.sh

备份linfan库的doudou表

[root@linfan ~]# mysqldump -uroot -p -h127.0.0.1 linfan doudou > tabledoudou-20180818.sqlEnter password:[root@linfan ~]# lsall-20180818.sql  anaconda-ks.cfg  doudou.repo  lin  nfs.sh  tabledoudou-20180818.sql

备份linfan数据库

[root@linfan ~]# mysqldump -uroot -p -h127.0.0.1 --databases linfan > data-linfan-20180818.sqlEnter password:[root@linfan ~]# lsall-20180818.sql  anaconda-ks.cfg  data-linfan-20180818.sql  doudou.repo  lin  nfs.sh  tabledoudou-20180818.sql

模拟误删linfan数据库

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

2.3 mysql数据恢复

[root@linfan ~]# lsall-20180818.sql  anaconda-ks.cfg  data-linfan-20180818.sql  doudou.repo  lin  nfs.sh  tabledoudou-20180818.sql[root@linfan ~]# mysql -uroot -p -h127.0.0.1 < all-20180818.sqlEnter password:[root@linfan ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'Enter password:+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+

恢复linfan数据库的doudou表

Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> source tabledoudou-20180818.sql; mysql> show tables;+------------------+| Tables_in_linfan |+------------------+| doudou           || linfan           |+------------------+2 rows in set (0.00 sec)

模拟删除整个数据库

mysql> show tables;+------------------+| Tables_in_linfan |+------------------+| doudou           || linfan           |+------------------+2 rows in set (0.00 sec)mysql> show database;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)

恢复整个数据库

[root@linfan ~]# lsall-20180818.sql  anaconda-ks.cfg  data-linfan-20180818.sql  doudou.repo  lin  nfs.sh  tabledoudou-20180818.sql[root@linfan ~]# mysql -uroot -p -h127.0.0.1 < all-20180818.sqlEnter password:[root@linfan ~]# mysql -uroot -p -h127.0.0.1  -e 'show databases;'Enter password:+--------------------+| Database           |+--------------------+| information_schema || linfan             || mysql              || performance_schema || sys                |+--------------------+

3.二进制格式mysql安装

下载二进制格式的mysql软件包

[root@linfan ~]# cd /usr/src/[root@linfan src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

创建用户和组

[root@linfan ~]# groupadd -r mysql[root@linfan ~]# useradd -M -s /sbin/nologin -g mysql mysql

解压软件至/usr/local/

[root@linfan src]# lsapr-1.6.3          apr-util-1.6.1          debug    mysql-5.7.22-linux-glibc2.12-x86_64.tar.gzapr-1.6.3.tar.bz2  apr-util-1.6.1.tar.bz2  kernels[root@linfan src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/[root@linfan src]# ls  /usr/local/apache  apr-util  etc    include  lib64    mysql-5.7.22-linux-glibc2.12-x86_64  shareapr     bin       games  lib      libexec  sbin                                 src[root@linfan src]# cd  /usr/local/ [root@linfan local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql‘mysql’ -> ‘mysql-5.7.22-linux-glibc2.12-x86_64/’[root@linfan local]# lltotal 0drwxr-xr-x. 13 root root 152 Aug 17 12:46 apachedrwxr-xr-x.  6 root root  58 Aug 17 12:35 aprdrwxr-xr-x.  5 root root  43 Aug 17 12:40 apr-utildrwxr-xr-x.  2 root root   6 Nov  5  2016 bindrwxr-xr-x.  2 root root   6 Nov  5  2016 etcdrwxr-xr-x.  2 root root   6 Nov  5  2016 gamesdrwxr-xr-x.  2 root root   6 Nov  5  2016 includedrwxr-xr-x.  2 root root   6 Nov  5  2016 libdrwxr-xr-x.  2 root root   6 Nov  5  2016 lib64drwxr-xr-x.  2 root root   6 Nov  5  2016 libexeclrwxrwxrwx.  1 root root  36 Aug 17 13:54 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/drwxr-xr-x.  9 root root 129 Aug 17 13:30 mysql-5.7.22-linux-glibc2.12-x86_64drwxr-xr-x.  2 root root   6 Nov  5  2016 sbindrwxr-xr-x.  5 root root  49 Jul 11 15:44 sharedrwxr-xr-x.  2 root root   6 Nov  5  2016 src

修改目录/usr/locaal/mysql的属主属组

[root@linfan ~]# chown -R mysql.mysql /usr/local/mysql[root@linfan ~]# ll /usr/local/mysql -dlrwxrwxrwx. 1 mysql mysql 36 Aug 17 13:54 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/

添加环境变量

[root@linfan ~]# ls /usr/local/mysqlbin  COPYING  docs  include  lib  man  README  share  support-files[root@linfan ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh[root@linfan ~]# . /etc/profile.d/mysql.sh[root@linfan ~]# echo $PATH/usr/local/mysql/bin:/usr/local/apache/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

建立数据存放目录

[root@linfan ~]# cd /usr/local/mysql[root@linfan mysql]# mkdir /opt/data[root@linfan mysql]# chown -R mysql.mysql /opt/data/[root@linfan mysql]# ll /opt/total 0drwxr-xr-x. 2 mysql mysql   6 Aug 17 14:05 datadrwxr-xr-x. 8 root  root  220 Jul 18 17:09 lin.d

初始化数据库

[root@linfan mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/2018-08-17T06:08:33.347313Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2018-08-17T06:08:33.873415Z 0 [Warning] InnoDB: New log files created, LSN=457902018-08-17T06:08:33.953310Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2018-08-17T06:08:34.016549Z 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: f8e46285-a1e3-11e8-b6bf-000c29c9d4ed.2018-08-17T06:08:34.019542Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2018-08-17T06:08:34.023380Z 1 [Note] A temporary password is generated for root@localhost: B

生成配置文件

[root@linfan ~]# 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> EOF
    

配置服务启动脚本

[root@linfan ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld[root@linfan ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld[root@linfan ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

启动mysql

[root@linfan ~]# service mysqld startStarting MySQL.Logging to '/opt/data/linfan.err'. SUCCESS![root@linfan ~]# ps -ef|grep mysqlroot      52200      1  0 14:25 pts/1    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pidmysql     52378  52200  4 14:25 pts/1    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=linfan.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306root      52408   2998  0 14:25 pts/1    00:00:00 grep --color=auto mysql[root@linfan ~]# ss -antlState      Recv-Q Send-Q                Local Address:Port                               Peer Address:PortLISTEN     0      128                               *:22                                            *:*LISTEN     0      100                       127.0.0.1:25                                            *:*LISTEN     0      128                              :::80                                           :::*LISTEN     0      128                              :::22                                           :::*LISTEN     0      100                             ::1:25                                           :::*LISTEN     0      80                               :::3306                                         :::*

修改密码

使用临时密码修改

[root@linfan ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.22Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> set password = password('linfan123');Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> quitBye