mysql进阶

[toc]

下载包

导入

开始安装

[root@localhost ~]# ls
anaconda-ks.cfg  mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
// 创建用户和组
[root@localhost ~]# id mysql
id: ‘mysql’: no such user
[root@localhost ~]# useradd -M -s /sbin/nologin -g mysql
useradd: group 'mysql' does not exist
[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -M -s /sbin/nologin -g mysql mysql
// 解压软件至/usr/local/
[root@localhost ~]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.33-linux-glibc2.12-x86_64  share
// 做软链接
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.33-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 19  2020 bin
drwxr-xr-x. 2 root root   6 May 19  2020 etc
drwxr-xr-x. 2 root root   6 May 19  2020 games
drwxr-xr-x. 2 root root   6 May 19  2020 include
drwxr-xr-x. 2 root root   6 May 19  2020 lib
drwxr-xr-x. 3 root root  17 Mar 24 09:08 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
lrwxrwxrwx. 1 root root  36 Apr 30 14:28 mysql -> mysql-5.7.33-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Apr 30 14:27 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Mar 24 09:08 share
drwxr-xr-x. 2 root root   6 May 19  2020 src
// 修改目录/usr/local/mysql的所属用户和所属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
// 设置环境变量
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# . /etc/profile.d/mysql.sh
[root@localhost local]# source /etc/profile.d/mysql.sh
// 建立数据存放目录
[root@localhost local]# mkdir /opt/data
[root@localhost local]# ll /opt/
total 0
drwxr-xr-x. 2 root root 6 Apr 30 14:30 data
[root@localhost local]# chown -R mysql.mysql /opt/data/
[root@localhost local]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Apr 30 14:30 data
//初始化数据库
[root@localhost local]# mysqld --initialize --user=mysql --datadir=/opt/data/
2021-04-30T06:38:19.407311Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-30T06:38:19.593705Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-30T06:38:19.615214Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-30T06:38:19.620337Z 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: a6e926f6-a97e-11eb-86ec-000c298fedd0.
2021-04-30T06:38:19.620834Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-04-30T06:38:20.261213Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-30T06:38:20.466555Z 1 [Note] A temporary password is generated for root@localhost: sAtFR5aEKz?(
// 密码
sAtFR5aEKz?(
// 写入文件方便以后使用
[root@localhost local]# echo 'sAtFR5aEKz?(' >pass
[root@localhost local]# ls
bin  games    lib    libexec  mysql-5.7.33-linux-glibc2.12-x86_64  sbin   src
etc  include  lib64  mysql    pass                                 share
[root@localhost local]# cat pass
sAtFR5aEKz?(
// 生成配置文件
[root@localhost local]# 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@localhost local]# cd /usr/local/mysql
[root@localhost mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@localhost support-files]# file mysql.server
mysql.server: POSIX shell script, ASCII text executable
[root@localhost support-files]# cp mysql.server /etc/init.d/mysqld
[root@localhost support-files]# cd
[root@localhost ~]# ll /etc/init.d/mysqld
-rwxr-xr-x. 1 root root 10576 5月   3 9:12 /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql  //添加路径
datadir=/opt/data  //添加路径
[root@localhost ~]# ss -antl
State    Recv-Q   Send-Q      Local Address:Port       Peer Address:Port   Process   
LISTEN   0        128               0.0.0.0:22              0.0.0.0:*                
LISTEN   0        128                  [::]:22                 [::]:*  
// 启动mysql
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost ~]# ss -antl
State    Recv-Q   Send-Q      Local Address:Port       Peer Address:Port   Process   
LISTEN   0        128               0.0.0.0:22              0.0.0.0:*                
LISTEN   0        128                  [::]:22                 [::]:*                
LISTEN   0        80                      *:3306                  *:*     
[root@localhost ~]# ps -ef|grep mysql
root       10207       1  0 15:07 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql      10395   10207  0 15:07 pts/0    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=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root       10428    1527  0 15:09 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# dnf whatprovides libncurses.so.5
Last metadata expiration check: 0:07:24 ago on Fri 30 Apr 2021 03:04:32 PM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : baseos
Matched from:
Provide    : libncurses.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs
Last metadata expiration check: 0:07:52 ago on Fri 30 Apr 2021 03:04:32 PM CST.
Dependencies resolved.
=====================================================================================
 Package                   Architecture Version                   Repository    Size
=====================================================================================
Installing:
 ncurses-compat-libs       x86_64       6.1-7.20180224.el8        baseos       331 k

Transaction Summary
=====================================================================================
Install  1 Package

Total download size: 331 k
Installed size: 1.2 M
Downloading Packages:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64.rpm    110 kB/s | 331 kB     00:03    
-------------------------------------------------------------------------------------
Total                                                 61 kB/s | 331 kB     00:05     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                             1/1 
  Installing       : ncurses-compat-libs-6.1-7.20180224.el8.x86_64               1/1 
  Running scriptlet: ncurses-compat-libs-6.1-7.20180224.el8.x86_64               1/1 
  Verifying        : ncurses-compat-libs-6.1-7.20180224.el8.x86_64               1/1 

Installed:
  ncurses-compat-libs-6.1-7.20180224.el8.x86_64                                      

Complete!
[root@localhost ~]# mysql -uroot -p'sAtFR5aEKz?('
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> set password=password('hzy123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
// 退出
mysql> exit
Bye
[root@localhost ~]# ls /usr/local/mysql
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost ~]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@localhost ~]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost ~]# ldconfig

备份

全量备份：全部备份

增量备份：备份全量备份后产生的新文件

差异备份：备份上一次的完全备份后发生变化的文件

给mysqldump软连接

[root@localhost ~]# ln -fs /usr/local/mysql/bin/mysqldump /usr/bin
[root@localhost ~]# ln -fs /usr/local/mysql/bin/mysql /usr/bin

全量备份

[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-1.sql
Enter password: 
[root@localhost ~]# ls
all-1.sql  anaconda-ks.cfg

备份表

[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 hzy > 2.sql
Enter password: 
[root@localhost ~]# ls
2.sql      anaconda-ks.cfg
all-1.sql  

备份库

[root@localhost ~]# mysqldump -uroot -p -h127.0.0.1 --databases hzy > 3.sql
Enter password: 
[root@localhost ~]# ls
2.sql  all-1.sql        
3.sql  anaconda-ks.cfg

删除库并恢复

mysql> drop database hzy;
Query OK, 1 row affected (0.00 sec)

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

mysql> source 3.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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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_hzy |
+---------------+
| hzy           |
+---------------+
1 row in set (0.00 sec)