Mysql进阶
mysql源码安装
//下载源码包
[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# ls
anaconda-ks.cfg mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
//创建mysql用户
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql
[root@localhost ~]# id mysql
uid=994(mysql) gid=991(mysql) groups=991(mysql)
[root@localhost ~]# grep mysql /etc/group
mysql:x:991:
//解压到/usr/local下
[root@localhost ~]# tar xf mysql-5.7.31-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.31-linux-glibc2.12-x86_64 share
//设置软链接
[root@localhost ~]# ln -s /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx. 1 root root 46 Dec 28 18:35 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 9 7161 31415 129 Jun 2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Nov 16 18:43 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
//设置所有者和所有组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql*
[root@localhost ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx. 1 mysql mysql 46 Dec 28 18:35 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Jun 2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Nov 16 18:43 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
[root@localhost mysql]# ls
bin docs include lib LICENSE man mysql README share support-files
//设置环境变量
[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 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql
//映射include
[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost mysql]# ll
total 288
drwxr-xr-x. 2 mysql mysql 4096 Jun 2 2020 bin
drwxr-xr-x. 2 mysql mysql 55 Jun 2 2020 docs
drwxr-xr-x. 3 mysql mysql 4096 Jun 2 2020 include
drwxr-xr-x. 5 mysql mysql 205 Jun 2 2020 lib
-rw-r--r--. 1 mysql mysql 275393 Jun 2 2020 LICENSE
drwxr-xr-x. 4 mysql mysql 30 Jun 2 2020 man
lrwxrwxrwx. 1 root root 19 Dec 28 18:43 mysql -> /usr/include/mysql/
-rw-r--r--. 1 mysql mysql 587 Jun 2 2020 README
drwxr-xr-x. 28 mysql mysql 4096 Jun 2 2020 share
drwxr-xr-x. 2 mysql mysql 90 Jun 2 2020 support-files
//设置帮助文档
[root@localhost mysql]# vim /etc/man_db.conf
MANDATORY_MANPATH /usr/local/mysql/man
//设置lib库
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost mysql]# ldconfig
//创建数据存放目录
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 28 18:51 data
//初始化数据库
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data
2020-12-28T10:53:56.679439Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-28T10:53:56.912979Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-28T10:53:56.964328Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-28T10:53:57.029586Z 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: fbe8d2ec-48fa-11eb-a6f8-000c29f9ec35.
2020-12-28T10:53:57.030609Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-28T10:53:57.732092Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-28T10:53:58.076815Z 1 [Note] A temporary password is generated for root@localhost: ,sWg3*=YF!ik
//保存临时密码
[root@localhost ~]# echo ',sWg3*=YF!ik' > pass
[root@localhost ~]# ls
anaconda-ks.cfg mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz pass
[root@localhost ~]# cat pass
,sWg3*=YF!ik
//写配置文件
[root@localhost ~]# 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
//配置服务启动脚本
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# ll /etc/init.d/mysqld
-rwxr-xr-x. 1 root root 10576 Dec 28 19:07 /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/opt/data
//启动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
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
//修改密码
[root@localhost ~]# cat pass
sWg3*=YF!ik
[root@localhost ~]# mysql -uroot -p',sWg3*=YF!ik'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
//查找是需要装哪个依赖包
[root@localhost ~]# yum whatprovides libncurses.so.5
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 0:18:09 ago on Mon 28 Dec 2020 03:33:21 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
//用临时密码登录
[root@localhost ~]# mysql -uroot -p',sWg3*=YF!ik'
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 16
Server version: 5.7.31
Copyright (c) 2000, 2020, 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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
//设置开机自动启动
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]# chkconfig --list
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
mysql配置文件
mysql的配置文件为/etc/my.cnf
[root@localhost ~]# vim .my.cnf
[client]
user=root
password=123456
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
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将无法正常处理连接请求 |
mysql破解密码步骤
1.改配置文件
[root@localhost ~]# vim /etc/my.cnf
skip-grant-tables
2.重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
3.登录数据库并修改密码
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> select * from userG;
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2020-12-28 19:26:13
password_lifetime: NULL
account_locked: N
*************************** 2. row ***************************
Host: localhost
User: mysql.session
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2020-12-28 18:53:58
password_lifetime: NULL
account_locked: Y
*************************** 3. row ***************************
Host: localhost
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2020-12-28 18:53:58
password_lifetime: NULL
account_locked: Y
3 rows in set (0.00 sec)
mysql> update user set authentication_string=password('654321') where User='root' and Host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> quit
Bye
4.修改配置文件，删除skip-grant-tables
[root@localhost ~]# vim /etc/my.cnf
删除skip-grant-tables
5.重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
6.验证密码是否修改成功
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# mysql -uroot -p654321
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 3
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
mysql数据库备份与恢复
数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
mysql备份工具mysqldump
//准备工作
mysql> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> use school;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.02 sec)
mysql> insert student(name,age) values('tom',20),('jerry',15),('tom',23);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 15 |
| 3 | tom | 23 |
+----+-------+------+
3 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student |
+------------------+
1 row in set (0.00 sec)
mysql> quit
Bye
//备份所有数据库
[root@localhost ~]# mysqldump -uroot -p654321 --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all.sql anaconda-ks.cfg mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz pass
[root@localhost ~]# file all.sql
all.sql: UTF-8 Unicode text, with very long lines
//不小心删除数据库
[root@localhost ~]# mysql -uroot -p654321 -e 'drop database school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
//恢复数据库
[root@localhost ~]# mysql -uroot -p654321 < all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
[root@localhost ~]# mysql -uroot -p654321 -e 'select * from school.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 20 |
| 2 | jerry | 15 |
| 3 | tom | 23 |
+----+-------+------+