myslq的二进制安装与备份（全备）
mysql的二进制安装
下载安装（这里使用的是一台全新的虚拟机）
[root@localhost ~]# dnf install -y wget vim
[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
创建用户和组
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql
解压软件至/usr/local/
[root@localhost ~]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local
创建软链接或者是修改目录名
[root@localhost ~]# cd /usr/local
[root@host local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
[root@host local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root 6 5月 18 2020 bin
drwxr-xr-x. 2 root root 6 5月 18 2020 etc
drwxr-xr-x. 2 root root 6 5月 18 2020 games
drwxr-xr-x. 2 root root 6 5月 18 2020 include
drwxr-xr-x. 2 root root 6 5月 18 2020 lib
drwxr-xr-x. 3 root root 17 3月 29 13:05 lib64
drwxr-xr-x. 2 root root 6 5月 18 2020 libexec
drwxr-xr-x. 9 mysql mysql 129 5月 6 00:47 mysql
drwxr-xr-x. 2 root root 6 5月 18 2020 sbin
drwxr-xr-x. 5 root root 49 3月 29 13:05 share
drwxr-xr-x. 2 root root 6 5月 18 2020 src
添加环境变量
[root@host local]# vim /etc/profile.d/mysql.sh
[root@host local]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@host local]# . /etc/profile.d/mysql.sh
[root@host local]# which mysql
/usr/local/mysql/bin/mysql
建立数据存放目录
[root@host local]# mkdir /opt/mysql-data
[root@host local]# chown -R mysql.mysql /opt/mysql-data/
[root@host local]# ll /opt
总用量 0
drwxr-xr-x. 2 mysql mysql 6 5月 5 17:14 mysql-data
初始化
[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql-data/
2021-05-06T04:50:14.889168Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-06T04:50:15.138981Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-06T04:50:15.206397Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-06T04:50:15.280368Z 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: 8c6be519-ae26-11eb-924f-000c296364bb.
2021-05-06T04:50:15.281289Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-06T04:50:16.195457Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-06T04:50:16.546203Z 1 [Note] A temporary password is generated for root@localhost: ?nFWIsQl,9>Q //这里是密码
编写配置文件
[root@localhost local]# vim /etc/my.cnf
[root@localhost local]# cat /etc/my.cnf
[mysqld]
datadir = /opt/mysql-data
basedir = /usr/local/mysql
datadir = /opt/mysql-data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql-data/mysql.pid
user = mysql
skip-name-resolve
配置服务启动脚本
[root@localhost local]# cd /usr/local/mysql
[root@localhost mysql]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost mysql]# sed -ri 's#^(datadir=).*#\1/opt/mysql-data#g' /etc/init.d/mysqld
[root@localhost mysql]# head -47 /etc/init.d/mysqld |tail -2
basedir=/usr/local/mysql
datadir=/opt/mysql-data
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/opt/mysql-data/localhost.localdomain.err'.
SUCCESS!
开机自启
[root@localhost mysql]# chkconfig mysqld on
[root@localhost mysql]# chkconfig --list
注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。
要列出 systemd 服务，请执行 'systemctl list-unit-files'。
查看在具体 target 启用的服务请执行
'systemctl list-dependencies [target]'。
mysqld 0:关 1:关 2:开 3:开 4:开 5:开 6:关
[root@localhost mysql]# ss -anlt
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 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
头文件和库文件配置
[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@localhost mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost mysql]# ldconfig
启动并设置密码
[root@localhost mysql]# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost mysql]# dnf provides libncurses.so.5
上次元数据过期检查：0:20:44 前，执行于 2021年05月06日 星期四 00时36分21秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库 ：baseos
匹配来源：
提供 : libncurses.so.5
[root@localhost mysql]# dnf install -y ncurses-compat-libs
[root@localhost mysql]# mysql -uroot -p
Enter password:
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('yzy123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysqldump
mysql的备份工具
语法:
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
备份整个数据库(全备)
[root@localhost mysql]# mysqldump -uroot -p --all-databases > all-66.sql
Enter password:
[root@localhost mysql]# ls
all-66.sql bin docs include lib LICENSE man README share support-files
备份某个数据库
[root@localhost mysql]# mysqldump -uroot -p --databases yzy > database-yzy.sql
Enter password:
[root@localhost mysql]# ls
all-66.sql bin database-yzy.sql docs include lib LICENSE man README share support-files
备份某个表
[root@localhost mysql]# mysqldump -uroot -p yzy happy > table-happy.sql
Enter password:
[root@localhost mysql]# ls
all-66.sql database-yzy.sql include LICENSE README support-files
bin docs lib man share table-happy.sql
数据恢复
删除表与恢复表
mysql> use yzy;
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 table;
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 '' at line 1
mysql> show tables
-> ;
+---------------+
| Tables_in_yzy |
+---------------+
| happy |
+---------------+
1 row in set (0.00 sec)
mysql> drop table happy;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.01 sec)
mysql> source table-happy.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, 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.01 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, 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_yzy |
+---------------+
| happy |
+---------------+
1 row in set (0.00 sec)
删除数据库并恢复
mysql> drop database if exists yzy;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> source all-66.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yzy |
+--------------------+
5 rows in set (0.01 sec)