mysql二进制安装
首先，需要把下载好的压缩包，拖进虚拟机内，我用的是findshell
[root@localhost ~]# ls
mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
anaconda-ks.cfg
[root@localhost ~]# id mysql
id: “mysql”：无此用户
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql
[root@localhost ~]# id mysql
uid=974(mysql) gid=973(mysql) 组=973(mysql)
[root@localhost ~]# grep mysql /etc/group
mysql:x:973:
这里需要创建用户和组
接下来就需要解压这个压缩包
[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
解压完成后在/usr/local/
下面就会有解压好的文件
这里给这个解压好的包设置一个别名，方便好用
[root@localhost ~]# ln -s /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost ~]# ll /usr/local/
总用量 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 4月 7 05:49 lib64
drwxr-xr-x. 2 root root 6 5月 18 2020 libexec
lrwxrwxrwx. 1 root root 46 5月 3 06:13 mysql -> /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 5月 3 06:10 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 5月 18 2020 sbin
drwxr-xr-x. 5 root root 49 4月 7 05:49 share
drwxr-xr-x. 2 root root 6 5月 18 2020 src
接下来需要把mysql这个包的属组改成mysql
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql*
[root@localhost ~]# ll /usr/local/
总用量 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 4月 7 05:49 lib64
drwxr-xr-x. 2 root root 6 5月 18 2020 libexec
lrwxrwxrwx. 1 mysql mysql 46 5月 3 06:13 mysql -> /usr/local/mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 5月 3 06:10 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 5月 18 2020 sbin
drwxr-xr-x. 5 root root 49 4月 7 05:49 share
drwxr-xr-x. 2 root root 6 5月 18 2020 src
在这里，你需要写一个环境变量
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql
创建一个目录，把这个目录的属组改成mysql
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# ll /opt
总用量 0
drwxr-xr-x. 2 root root 6 5月 3 08:05 data
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt
总用量 0
drwxr-xr-x. 2 mysql mysql 6 5月 3 08:05 data
初始化
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data
2021-05-03T12:07:50.450149Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-03T12:07:50.634722Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-03T12:07:50.669930Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-03T12:07:50.726011Z 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: 2ea5b650-ac08-11eb-8206-000c29486c24.
2021-05-03T12:07:50.726672Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-03T12:07:51.234954Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-03T12:07:51.367218Z 1 [Note] A temporary password is generated for root@localhost: p-Mjq*(pE3)g
[root@localhost ~]# echo 'p-Mjq*(pE3)g' > yh
最后一行是临时密码，需要记住。每一次初始化得来得临时密码都不一样
初始化完成后，会生成这些文件
[root@localhost ~]# ls /opt/data
auto.cnf client-cert.pem ibdata1 mysql public_key.pem sys
ca-key.pem client-key.pem ib_logfile0 performance_schema server-cert.pem
ca.pem ib_buffer_pool ib_logfile1 private_key.pem server-key.pem
编写配置文件
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# 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@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# ls
bin docs include lib LICENSE man README share 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 06:45 /etc/init.d/mysqld
注意，这下面的文件是绿色的
[root@localhost ~]# vim /etc/init.d/mysqld
在这个配置文件里面找到这两个为空的位置，写上我的数据库在那个位置
basedir=/usr/local/mysql
datadir=/opt/data
启动服务
[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:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
查看进程
[root@localhost ~]# ps -ef |grep mysqld
root 75383 1 0 08:14 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql 75571 75383 0 08:14 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=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
这里直接使用密码登录发现登陆不上，此时需要看清楚报错的内容，
需要安装一个包，，那就去安装一个软件包
[root@localhost ~]# mysql -uroot -p'p-Mjq*(pE3)g'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost ~]# dnf whatprovides libncurses.so.5
上次元数据过期检查：0:18:10 前，执行于 2021年05月03日 星期一 08时01分37秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库 ：baseos
匹配来源：
提供 : libncurses.so.5
[root@localhost ~]# dnf -y install ncurses-compat-libs
上次元数据过期检查：0:18:34 前，执行于 2021年05月03日 星期一 08时01分37秒。
依赖关系解决。
======================================================================================
软件包 架构 版本 仓库 大小
======================================================================================
安装:
ncurses-compat-libs x86_64 6.1-7.20180224.el8 baseos 331 k
事务概要
======================================================================================
安装 1 软件包
总下载：331 k
安装大小：1.2 M
下载软件包：
ncurses-compat-libs-6.1-7.20180224.el8.x86_64.rpm 1.1 MB/s | 331 kB 00:00
--------------------------------------------------------------------------------------
总计 338 kB/s | 331 kB 00:00
警告：/var/cache/dnf/baseos-055ffcb2ec25a27f/packages/ncurses-compat-libs-6.1-7.20180224.el8.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID 8483c65d: NOKEY
CentOS Stream 8 - BaseOS 1.6 MB/s | 1.6 kB 00:00
导入 GPG 公钥 0x8483C65D:
Userid: "CentOS (CentOS Official Signing Key) <security@centos.org>"
指纹: 99DB 70FA E1D7 CE22 7FB6 4882 05B5 55B3 8483 C65D
来自: /etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
导入公钥成功
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
运行脚本: ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
验证 : ncurses-compat-libs-6.1-7.20180224.el8.x86_64 1/1
Installed products updated.
已安装:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
完毕！
安装完毕之后再使用密码登录，就可以等登录上了
[root@localhost ~]# mysql -uroot -p'p-Mjq*(pE3)g'
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('ZHANGde12+Jun')；
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
接下来，就需要做一个软链接
可以找到头文件
[root@localhost ~]# ln -s /usr/local/mysql/include /usr/include/mysql
库文件所放的位置
[root@localhost ~]# vim /etc/ld.so.conf.d/mysql.cnf
/usr/local/mysql/lib
[root@localhost ~]# ldconfig
重新读取一下库文件，这样就可以找到lib了
方便实用的配置文件
[root@localhost ~]# vim .my.cnf
[client]
user=root
password=ZHANGde12+Jun
二进制mysql安装步骤
下载安装包
创建用户
解压至指定目录
创建软链接
修改属主属组
设置环境变量
创建数据存放目录并修改属主属组
初始化并保存密码
修改配置文件
配置启动脚本和开机自启
头文件和库文件配置
启动并设置密码
mysql数据库备份与恢复
数据库常用备份方案
数据库备份方案：
全量备份
增量备份
差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快。备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备份后到进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
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
创建一个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database zdj;
Query OK, 1 row affected (0.00 sec)
mysql> use zdj; //进入数据库
Database changed
//创建表
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint,score float);
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| score | float | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
//给表插入数据
mysql> insert student(name,age) values('tom',20),('jerry',21),('zhangshan',29),('lisi',19);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 1 | tom | 20 | NULL |
| 2 | jerry | 21 | NULL |
| 3 | zhangshan | 29 | NULL |
| 4 | lisi | 19 | NULL |
+----+-----------+------+-------+
4 rows in set (0.00 sec)
mysql> show create table student; //查看表是怎么创建的
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//再创建一张表
mysql> CREATE TABLE `class1` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `name` varchar(50) DEFAULT NULL,
-> `age` tinyint(4) DEFAULT NULL,
-> `score` float DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables; //查看到有两张表
+---------------+
| Tables_in_zdj |
+---------------+
| class1 |
| student |
+---------------+
2 rows in set (0.00 sec)
//向class1插入数据
mysql> insert class1(name,score) values('tom',67.5),('jerry',77.5),('zhangshan',59.9),('lisi',88);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from class1；
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
+----+-----------+------+-------+
4 rows in set (0.00 sec)
全量备份
[root@localhost ~]# mysqldump -uroot -pZHANGde12+Jun --all-databases > all-$(date "+%Y%m%d").sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210503.sql initial-setup-ks.cfg yh
anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
备份并不是把里面的内容复制一份，而是重新创建一份，你有我就把你干掉，再创建，没有，我就直接创建
[root@localhost ~]# less all-20210503.sql //查看备份的内容
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mysql`;
--
-- Table structure for table `columns_priv`
--
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
`Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
//这里备份了我的zdj数据库里面两张表的内容
[root@localhost ~]# mysqldump -uroot -pZHANGde12+Jun zdj class1 student > all-lang.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-20210503.sql anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
all-lang.sql initial-setup-ks.cfg yh
针对表的备份
[root@localhost ~]# less all-lang.sql
DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `student`
--
LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'tom',20,NULL),(2,'jerry',21,NULL),(3,'zhangshan',29,NULL),(4,'lisi',19,NULL);
针对库的备份
[root@localhost ~]# less zdj.sql
Current Database: `zdj`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `zdj` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `zdj`;
--
-- Table structure for table `class1`
--
DROP TABLE IF EXISTS `class1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`score` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
接下来开始对库进行删除备份
首先删除这个数据库
[root@localhost ~]# mysql -e 'drop database zdj;'
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
//进入mysql创建一个数据库
然后再去读取那个库文件
mysql> create database yh;
Query OK, 1 row affected (0.00 sec)
mysql> use yh;
Database changed
mysql> source all-lang.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)
这里就读取出来了
mysql> show tables;
+--------------+
| Tables_in_yh |
+--------------+
| class1 |
| student |
+--------------+
2 rows in set (0.00 sec)
//表内的数据也同样的备份过来了。
mysql> select * from class1;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
+----+-----------+------+-------+
4 rows in set (0.00 sec)
这样的备份，可以不用之前的数据库的名字，这里我换了一个数据库的名字，也可以备份过来。这个数据可以恢复到另外一个数据库内
恢复数据库
< : 表示我的数据库来源于这个文件
输入重定向
[root@localhost ~]# mysql < zdj.sql
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
//恢复过来了
//数据库内的表也都恢复好了
[root@localhost ~]# mysql -e 'select * from zdj.class1;'
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 5 | tom | NULL | 67.5 |
| 6 | jerry | NULL | 77.5 |
| 7 | zhangshan | NULL | 59.9 |
| 8 | lisi | NULL | 88 |
+----+-----------+------+-------+
[root@localhost ~]# mysql -e 'drop database zdj;'
//删除我的数据库
[root@localhost ~]# mysql -e 'show databases;'
//查看
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]# mysql -e 'create database zdj;'
//创建一个数据库
[root@localhost ~]# mysql zdj < all-lang.sql
//把我备份的文件备份到刚刚创建的数据库里
[root@localhost ~]# mysql -e 'show tables from zdj;'
+---------------+
| Tables_in_zdj |
+---------------+
| class1 |
| student |
+---------------+
//查看备份进来了
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
