MySQL安装¶

# 官网地址
https://dev.mysql.com/downloads/mysql/
# 国内的tsinghua镜像
https://mirror.tuna.tsinghua.edu.cn/mysql/
# 国内的huwei镜像
https://mirrors.huaweicloud.com/mysql/Downloads/

https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port = 3306 
[mysql]
# 设置mysql客户端默认字符集   
default-character-set=utf8 
[mysqld]
# mysql服务端默认监听(listen on)的TCP/IP端口号
port=3306 
# 基准路径，其他路径都相对于这个路径 
basedir="D:\mysql" 
# mysql数据库文件所在目录
datadir="D:\mysql\data" 
# 默认字符集 
character-set-server=utf8mb4
# 默认存储引擎
default-storage-engine=INNODB
# SQL模式为strict模式
# sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 
# mysql服务器支持的最大并发连接数（用户数）。如果设置得过小而用户比较多，会经常出现“Too many connections”错误。
max_connections=1024
# 禁用查询缓存，这个参数在5.7.20后已经Deprecated
# query_cache_size=0 
# 内存中的每个临时表允许的最大大小。如果临时表大小超过该值，临时表将自动转为基于磁盘的表（Disk Based Table）。
tmp_table_size=34M 
# 缓存的最大线程数。当客户端连接断开时，如果客户端总连接数小于该值，则处理客户端任务的线程放回缓存。在高并发情况下，如果该值设置得太小，就会有很多线程频繁创建，线程创建的开销会变大，查询效率也会下降。一般来说如果在应用端有良好的多线程处理，这个参数对性能不会有太大的提高。
thread_cache_size=8 
default_authentication_plugin = mysql_native_password

# 不安全的初始化，默认root密码为空，需要登陆后自己设置一个密码
mysqld --initialize-insecure --console
# 直接初始化，为root生成一个随机密码，密码打印在控制台中
mysqld --initialize --console

mysqld -install MySQL --defaults-file="D:\mysql\my.ini" 

net start mysql 

mysql -u root -p
use mysql;
update user set authentication_string=PASSWORD("123456AWS!@#") where user="root";
flush privileges;
quit;

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.05 sec)

mysql>

sudo yum -y install libaio libncurses*
sudo apt-get install libaio1 libaio-dev libncurses5

$ groupadd mysql
$ useradd -r -g mysql -s /bin/false mysql
$ mkdir -p /data/mysql/{data,binlog} 
$ chown -R mysql:mysql /data/mysql

[client]
port = 3306
socket = /data/mysql/mysql.sock
#default-character-set　=　utf8mb4

[mysql]
no-auto-rehash
default-character-set = utf8mb4

[mysqld]
#skip-grant-tables
log_timestamps=SYSTEM
user = mysql
port = 3306
basedir = /usr/local/mysql
log_error = /data/mysql/mysql.error
datadir = /data/mysql/data/
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/db.pid
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
default_authentication_plugin = mysql_native_password
skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024
default_time_zone='+8:00'
# innodb_buffer_pool_size=32G
innodb_buffer_pool_instances=8
innodb_log_file_size=4G
innodb_log_files_in_group=4
# binlog过期时间3天
binlog_expire_logs_seconds=259200
wait_timeout = 9000
interactive_timeout = 9000
net_write_timeout=6000
net_read_timeout=6000
thread_cache_size=10
sync_binlog=800
innodb_autoextend_increment=64
open_files_limit=10200
#sort_buffer_size=256M

$ cd /tmp/

$ wget https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
# $ wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

$ tar  -zxvf  /tmp/mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz -C /usr/local
$ mv  /usr/local/mysql-8.0.23-linux-glibc2.12-x86_64  /usr/local/mysql
$ cd  /usr/local/mysql
$ ./bin/mysqld   --initialize-insecure  \
    --basedir=/usr/local/mysql \
    --datadir=/data/mysql/data \
    --user=mysql  \
    --pid-file=/data/mysql/mysql.pid

$ ./support-files/mysql.server start
$ ./support-files/mysql.server status

$ cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql
$ chkconfig --add mysql
$ chkconfig mysql on

/usr/local/mysql/bin/mysql -u root -p  
alter user root@'localhost' IDENTIFIED by 'Sj36668182' ;
-- UPDATE mysql.user SET authentication_string=PASSWORD("QH@123456") WHERE user='root' ;
grant all privileges on *.* to 'root' @'%' identified by '123456';
flush privileges;

echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
# echo "PATH=$PATH:/usr/local/mysql/bin" >> ~/.bashrc 
source /etc/profile

$ systemctl status mysql
$ systemctl start mysql
$ systemctl stop mysql
$ systemctl restart mysql

$ wget -P /tmp https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
$ wget -P /tmp https://dev.mysql.com/get/mysql80-community-release-el6-1.noarch.rpm
$ rpm -Uvh platform-and-version-specific-package-name.rpm

$ yum repolist all | grep mysql
$ dnf repolist all | grep mysql

$ sudo yum-config-manager --disable mysql80-community
$ sudo yum-config-manager --enable mysql57-community

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

 $ yum repolist enabled | grep mysql

$ sudo yum install mysql-community-server

$ sudo service mysqld start
$ sudo systemctl start mysqld.service
$ sudo service mysqld status
$ sudo service mysqld status

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

[mysqld]
default_authentication_plugin = mysql_native_password

GRANT ALL PRIVILEGES ON *.* TO `mike`@`%` IDENTIFIED BY '000000' WITH GRANT OPTION;

# mysql5.7.10(GA)版本之后默认的 default_password_lifetime 默认值为 0，表示禁用自动密码过期。默认全局配置：所有用户的密码永不过期
# 如果要建立全局策略，让所有用户的密码的使用期限为六个月，可在服务端配置文件 my.cnf 中修改 default_password_lifetime 配置项的值为 180。
# 如果是正整数 N ，则表示允许的设置密码生存周期 为 N，单位为天 。

-- 查看密码策略  
show variables like 'validate_password%';
-- 新密码不能和前面三次的密码相同
password_history = 3 ; 
-- 新密码不能和前面九十天内使用的密码相同
password_reuse_interval = 90 ; 
-- 密码验证策略低要求(0或LOW代表低级)
validate_password.policy=0;
-- 密码至少要包含的小写字母个数和大写字母个数
validate_password.mixed_case_count=0;
-- 密码至少要包含的数字个数
validate_password.number_count=0; 
-- 密码至少要包含的特殊字符数
validate_password.special_char_count=0; 
-- 密码至少长度
validate_password.length=6; 

-- 建用户
CREATE USER 'dba'@'%' IDENTIFIED WITH 'mysql_native_password' by 'admin@123' ;

-- 赋超级权限
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'%';

CREATE USER 'shendandan'@'192.168.%' IDENTIFIED WITH 'mysql_native_password' by 'admin@123' ;

-- 指定密码过期，以便用户第一次使用的时候需要修改密码
CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY 'new_password' PASSWORD EXPIRE DEFAULT;

-- 不使用加密连接
CREATE USER 'dba'@'%' IDENTIFIED WITH 'mysql_native_password' by 'admin@123'  REQUIRE NONE ;

-- 使用加密连接
CREATE USER 'dba'@'%' IDENTIFIED WITH 'mysql_native_password' by 'admin@123'  REQUIRE NONE ;


-- 创建用户并且设置资源闲置（如果不带资源选项，默认不限制资源）
-- 单位小时内账户被允许查询500次
-- 单位小时内账户被允许更新100次
-- 单位小时内最大连接数不限制
-- 同一时刻最大并发连接数不限制
CREATE USER 'jeffrey'@'%' 
IDENTIFIED WITH 'mysql_native_password' by 'admin@123'
WITH 
    MAX_QUERIES_PER_HOUR 500 
    MAX_UPDATES_PER_HOUR 100 
    MAX_CONNECTIONS_PER_HOUR 0 
    MAX_USER_CONNECTIONS 0;



-- 锁定用户
CREATE USER 'jeffrey'@'localhost' ACCOUNT LOCK
-- 解锁用户
ALTER USER 'jeffrey'@'localhost' ACCOUNT UNLOCK
-- 删除用户
DROP USER 'jeffrey'@'localhost';
-- 修改用户
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
-- 修改当前用户自己账号的密码
ALTER USER USER() IDENTIFIED BY 'new_password';
-- 修改其他用户账号的密码
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'new_password';
-- 修改用户认证插件
ALTER USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password;
-- 修改密码和插件
ALTER USER 'jeffrey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';


-- 详细语法

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'hash_string'
}

tls_option: {
   SSL
 | X509
 | CIPHER 'cipher'
 | ISSUER 'issuer'
 | SUBJECT 'subject'
}

resource_option: {
    MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
}

password_option: {
    PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
}

lock_option: {
    ACCOUNT LOCK
  | ACCOUNT UNLOCK
}

-- 表权限
grant select , insert , delete,alter , update ，create, drop  on db.table to user@'host';


-- 存储过程/函数权限
grant EXECUTE  , create routine , alter routine  on db.proc to user@'host';

-- 在MySQL8.0.20之前，需要对普通用户进行如下授权后才能看到其他用户定义的函数/存储过程：
GRANT CREATE ROUTINE,ALTER ROUTINE,EXECUTE,TRIGGER,SYSTEM_USER,EVENT,SELECT ON schema_name.* TO 'YOUR_USERNAME';

-- 在MySQL8.0.20后，可以这样赋权
GRANT CREATE ROUTINE,ALTER ROUTINE,EXECUTE,TRIGGER,SYSTEM_USER,EVENT,SHOW_ROUTINE  ON schema_name.* TO 'YOUR_USERNAME';


-- MySQL8.0.20中专门增加了show_routine 权限用于表示查看所有的 procedures/func 的权限（即使不是definer），不过需要注意，show_routine 是全局权限。

# for  Ubuntu or Debian
apt build-dep mariadb-server

# for Fedora or Rhel
dnf builddep mariadb-server

# for Centos
yum install yum-utils
yum-builddep mariadb-server

# for openSUSE or SUSE 
zypper source-install -d mariadb

cd /usr/local/src/ 

# 在国内镜像下载最新版的mariadb源代码
wget https://mirrors.neusoft.edu.cn/mariadb//mariadb-11.0.1/source/mariadb-11.0.1.tar.gz

tar -zxvf mariadb-11.0.1.tar.gz  && cd  ariadb-11.0.1


cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/mydata/data \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci