linux下安装mysql 8.0.13

阿里云Linux下安装mysql 8.0.13

1.下载安装包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz

2.解压

tar -xvf mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz

3.改名字

mv mysql-8.0.13-linux-glibc2.12-x86_64 mysql

4.移动位置,创建用户和用户组并授权

mv mysql /usr/local/
cd /usr/local/
ll
[root@linux local]# groupadd mysql
[root@linux local]# useradd -r -g mysql mysql
[root@linux local]# cd mysql/
#注意：进入mysql文件下授权所有的文件
[root@linux mysql]# ll
[root@linux mysql]# chown -R mysql:mysql ./
[root@linux mysql]# ll

5.创建data文件夹

[root@linux mysql]# mkdir data
[root@linux mysql]# ll

6.初始化数据库，(随机密码)

安装：yum -y install libaio、yum -y install numactl

[root@linux mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

2020-04-17T08:47:38.623880Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-04-17T08:47:38.624038Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server in progress as process 10415
2020-04-17T08:47:42.359922Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: dTMu)mewW06=
2020-04-17T08:47:44.048720Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server has completed

随机密码：dTMu)mewW06=
    
    VCRsjwh,q0-I

7.修改/usr/local/mysql当前目录得用户

[root@linux mysql]# ll
total 468
drwxr-xr-x  2 mysql mysql   4096 Apr 17 16:41 bin
drwxr-xr-x  6 mysql mysql   4096 Apr 17 16:47 data
drwxr-xr-x  2 mysql mysql   4096 Apr 17 16:41 docs
drwxr-xr-x  3 mysql mysql   4096 Apr 17 16:40 include
drwxr-xr-x  6 mysql mysql   4096 Apr 17 16:41 lib
-rw-r--r--  1 mysql mysql 335809 Oct  7  2018 LICENSE
-rw-r--r--  1 mysql mysql 101807 Oct  7  2018 LICENSE.router
drwxr-xr-x  4 mysql mysql   4096 Apr 17 16:40 man
-rw-r--r--  1 mysql mysql    687 Oct  7  2018 README
-rw-r--r--  1 mysql mysql    700 Oct  7  2018 README.router
drwxr-xr-x 28 mysql mysql   4096 Apr 17 16:41 share
drwxr-xr-x  2 mysql mysql   4096 Apr 17 16:41 support-files

[root@linux mysql]# chown -R root:root ./
[root@linux mysql]# chown -R mysql:mysql data

[root@linux mysql]# ll
total 468
drwxr-xr-x  2 root  root    4096 Apr 17 16:41 bin
drwxr-xr-x  6 mysql mysql   4096 Apr 17 16:47 data
drwxr-xr-x  2 root  root    4096 Apr 17 16:41 docs
drwxr-xr-x  3 root  root    4096 Apr 17 16:40 include
drwxr-xr-x  6 root  root    4096 Apr 17 16:41 lib
-rw-r--r--  1 root  root  335809 Oct  7  2018 LICENSE
-rw-r--r--  1 root  root  101807 Oct  7  2018 LICENSE.router
drwxr-xr-x  4 root  root    4096 Apr 17 16:40 man
-rw-r--r--  1 root  root     687 Oct  7  2018 README
-rw-r--r--  1 root  root     700 Oct  7  2018 README.router
drwxr-xr-x 28 root  root    4096 Apr 17 16:41 share

8.配置权限 # chmod 777 ./my-default.cnf

[root@linux mysql]# cd support-files/
[root@linux support-files]# ll
total 24
-rw-r--r-- 1 root root   773 Oct  7  2018 magic
-rwxr-xr-x 1 root root  1061 Oct  7  2018 mysqld_multi.server
-rwxr-xr-x 1 root root  2048 Oct  7  2018 mysql-log-rotate
-rwxr-xr-x 1 root root 10576 Oct  7  2018 mysql.server

[root@linux support-files]# touch my-default.cnf
[root@linux support-files]# chmod 777 ./my-default.cnf

[root@linux support-files]# ll
total 24
-rw-r--r-- 1 root root   773 Oct  7  2018 magic
-rwxrwxrwx 1 root root     0 Apr 17 16:53 my-default.cnf
-rwxr-xr-x 1 root root  1061 Oct  7  2018 mysqld_multi.server
-rwxr-xr-x 1 root root  2048 Oct  7  2018 mysql-log-rotate
-rwxr-xr-x 1 root root 10576 Oct  7  2018 mysql.server

[root@linux support-files]# cd ../
[root@linux mysql]# cp support-files/my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? 
[root@linux mysql]# cp support-files/my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y

出现错误，加/

[root@linux mysql]# \cp support-files/my-default.cnf /etc/my.cnf
[root@linux mysql]# 
[root@linux mysql]# vim /etc/my.cnf

[mysqld]
  
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
  
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
  
# These are commonly set, remove the # and set as required.
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
log-error = /usr/local/mysql/data/error.log
pid-file = /usr/local/mysql/data/mysql.pid
tmpdir = /tmp
port = 3306
#lower_case_table_names = 1
# server_id = .....
# socket = .....
#lower_case_table_names = 1
max_allowed_packet=32M
default-authentication-plugin = mysql_native_password
#lower_case_file_system = on
#lower_case_table_names = 1
log_bin_trust_function_creators = ON
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

9.开机自启，进入/usr/local/mysql/support-files进行设置

[root@linux mysql]# cd support-files/
[root@linux support-files]# ll
total 24
-rw-r--r-- 1 root root   773 Oct  7  2018 magic
-rwxrwxrwx 1 root root     0 Apr 17 16:53 my-default.cnf


cp mysql.server /etc/init.d/mysql

[root@linux support-files]# chmod +x /etc/init.d/mysql
[root@linux support-files]# ll
total 24
-rw-r--r-- 1 root root   773 Oct  7  2018 magic
-rwxrwxrwx 1 root root     0 Apr 17 16:53 my-default.cnf
-rwxr-xr-x 1 root root  1061 Oct  7  2018 mysqld_multi.server
-rwxr-xr-x 1 root root  2048 Oct  7  2018 mysql-log-rotate
-rwxr-xr-x 1 root root 10576 Oct  7  2018 mysql.server

10.注册服务,查看

[root@linux support-files]# cd ..
[root@linux mysql]# chkconfig --add mysql
[root@linux mysql]# chkconfig --list mysql

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]'.

mysql           0:off   1:off   2:on    3:on    4:on    5:on    6:off

11.etc/ld.so.conf要配置路径，不然报错

[root@linux mysql]# vim /etc/ld.so.conf

include ld.so.conf.d/*.conf
/usr/local/mysql/lib
~                                                                                                       
~

12.配置环境变量

[root@linux mysql]# vim /etc/profile

# /etc/profile
export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib


[root@linux mysql]# source /etc/profile
[root@linux mysql]# service mysql start
Starting MySQL.Logging to '/usr/local/mysql/data/error.log'.
.                                                          [  OK  ]

13.登录

cd /usr/local/mysql
alias mysql=/usr/local/mysql/bin/mysql
[root@linux mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.13

Copyright (c) 2000, 2018, 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.

14.修改密码

mysql>  alter user 'root'@'localhost' identified by 'XXXXXXX';   
Query OK, 0 rows affected (0.09 sec)

15.开启Navicat远程连接

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
Database changed
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | $A$005$t8Y<0i1
                                               ,E|P`ZT)zSQTUDAQ5V86TvCsNfx1TRQagWpjnmlUQy0vnAu7awf2 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

mysql> GRANT ALL ON *.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1234'; 
Query OK, 0 rows affected (0.09 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye
[root@linux mysql]# service mysql restart
Shutting down MySQL..                                      [  OK  ]
Starting MySQL..                                           [  OK  ]

连接失败，改

[root@linux mysql]# mysql -uroot -p        
Enter password: 
mysql> use mysql
mysql> update user set host='%' where user='root' limit 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *A4B6157319038724E3560894F7F932C8886EBFCF                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

mysql> exit;
Bye
[root@linux mysql]# service mysql restart
Shutting down MySQL...                                     [  OK  ]
Starting MySQL..                                           [  OK  ]

16.连接成功

17.出现错误

输入mysql -u root -p出现错误：-bash: mysql: command not found
输入：alias mysql=/usr/local/mysql/bin/mysql

18.卸载mysql

rpm -qa|grep -i mysql查看是否安装过mysql，如果安装过需要先卸载掉

rpm -e

查找命令find / -name mysql

rm -rf

19.添加用户组和权限

create user 'cs'@'cs' identified by '123456'; 
grant select,insert,delete,update on cs.cs1 to 'cs'@'cs';

1.创建用户:
# 指定ip：192.118.1.1的mjj用户登录
create user 'mjj'@'192.118.1.1' identified by '123';
# 指定ip：192.118.1.开头的mjj用户登录
create user 'mjj'@'192.118.1.%' identified by '123';
# 指定任何ip的mjj用户登录
create use 'mjj'@'%' identified by '123';

2.删除用户
drop user '用户名'@'IP地址';
3.修改用户
rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

4.修改密码
set password for '用户名'@'IP地址'=Password('新密码');

#查看权限
show grants for '用户'@'IP地址'

#授权
grant select ,insert,update on db1.t1 to "mjj"@'%';

# 表示有所有的权限，除了grant这个命令，这个命令是root才有的。
grant all privileges  on db1.t1 to "mjj"@'%';

#取消权限
取消来自远程服务器的mjj用户对数据库db1的所有表的所有权限

revoke all on db1.* from 'mjj'@"%";  

取消来自远程服务器的mjj用户所有数据库的所有的表的权限
revoke all privileges on '*' from 'mjj'@'%';

20.备份

mysqldump -u用户名 -p密码 数据库 > 导出的文件名

# 备份：数据表结构+数据
mysqdump -u root db1 > db1.sql -p


# 备份：数据表结构
mysqdump -u root -d db1 > db1.sql -p

#导入现有的数据到某个数据库
#1.先创建一个新的数据库
create database db10;
# 2.将已有的数据库文件导入到db10数据库中
mysqdump -u root -d db10 < db1.sql -p

1.导出整个数据库

mysqldump -u用户名 -p密码 数据库 > 导出的文件名

2.导出一个数据表

mysqldump -u用户名 -p密码 数据库名 表名> 导出的文件名

两者之间的差别在于不使用 --databases 选项，则备份输出信息中不会包含CREATE DATABASE或USE语句。不使用 --databases 选项备份的数据文件，在后期进行数据还原操作时，如果该数据库不存在，必须先创建该数据库。

3.批量导出多个数据库

mysqldump -u用户名 -p密码 --databases 数据库 导出的文件名
mysqldump -u用户名 -p密码 -B 数据库 导出的文件名

4.导出所有数据库

mysqldump -u用户名 -p密码 -all-databases 导出的文件名

5.还原数据库

source命令要进入mysql控制台使用

SOURCE 导入文件的路径;

1.登录 MySQL
2.选中数据库
3.设置数据库编码，如果不设置，可能会出现乱码
4.使用 SOURCE 命令还原数据
5.查看还原结果

设置数据库编码

-- 客户端的编码
SET character_set_client = utf8;

-- 连接的编码
SET character_set_connection = utf8;

-- 查询的结果编码
SET character_set_results = utf8;

-- 数据库编码
SET character_set_database = utf8;

-- 数据库服务器的编码
SET character_set_server = utf8;

数据库

显示
show databases;
创建
create database cs2;
删除
drop database cs2;

在命令行上运行，在cs2上还原
mysql -uroot -p1234 cs2 < /root/cs1.sql

• 全备份将备份某一时刻所有的数据
• 增量备份仅备份某一段时间内发生过改变的数据。
• 通过物理或逻辑备份工具就可以完成完全备份，而增量备份需要开启MySQL二进制日志，通过日志记录数据的改变，从而实现增量差异备份。

mysqldump -uroot -p cs > /root/cs12.sql

mysqldump -uroot -p --databases cs > /root/cs11.sql

-rw-r--r--  1 root root      2113 6月  23 09:05 cs11.sql
-rw-r--r--  1 root root      1952 6月  23 09:05 cs12.sql

两者之间的差别在于不使用 --databases 选项，则备份输出信息中不会包含CREATE DATABASE或USE语句。不使用 --databases 选项备份的数据文件，在后期进行数据还原操作时，如果该数据库不存在，必须先创建该数据库。