MySQL5.7

check network

ip a

######yum localinstall mysql-community-common-5.7.26-1.el7.x86_64.rpm mysql-community-libs-5.7.26-1.el7.x86_64.rpm mysql-community-client-5.7.26-1.el7.x86_64.rpm mysql-community-server-5.7.26-1.el7.x86_64.rpm -y

common ---> libs ---> client ---> server

yum localinstall -y mysql-commercial-common-5.7.26-1.1.el7.x86_64.rpm mysql-commercial-libs-5.7.26-1.1.el7.x86_64.rpm mysql-commercial-client-5.7.26-1.1.el7.x86_64.rpm mysql-commercial-server-5.7.26-1.1.el7.x86_64.rpm -y

vim /etc/selinux/config SELINUX=disabled

systemctl start mysqld

systemctl enable mysqld

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

if no newwork mount CD:

mount /dev/sr0 /mnt

cd /etc/yum.repos.d/

touch media.repo

vim media.repo

[media]
baseurl = file:///mnt
gpgcheck = 0
enabled = 1

備份復原 MysqlDump

# 備份
mysqldump -u<用戶> -p<密碼> <資料庫名稱> > ~/Desktop/<資料庫名稱.sql>
mysqldump --user=<用戶> --password=<密碼> --all-databases > ~/Desktop/<匯入檔案或指令.sql>
# 復原
mysql --user=用戶 --password=密碼 < ~/Desktop/<匯入檔案或指令.sql>

FLUSH PRIVILEGES;

check process

netstat -ltnp

mysql enterprise backup

[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
#
# 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
datadir=/mysqldata/data
socket=/mysqldata/data/mysql.sock


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid




server-id=<server-id>
log-bin=/mysqldata/binlog/mybin.log


plugin-load=audit_log.so
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_file=/mysqlbk/auditlog/myaudit.log


innodb_buffer_pool_size=6G
innodb_log_file_size=256M
max_connections=10000
character-set-server=utf8
collation-server=utf8_unicode_ci


[mysqld_safe]
character-set-server=utf8
[client]
socket=/mysqldata/data/mysql.sock

sudo yum -y install mariadb-server
# 關閉防火牆 --add-port=3306/tcp
firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --reload
# 設置開機自啟服務
systemctl enable mariadb
systemctl start mariadb
# 初始化安全性設置
mysql_secure_installation

# 登入 MySQL 設置用戶權限
mysql -u root -p

USE mysql;
# <資料庫名>.<表名>，*.* 表示全部資料庫及所有表
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.%.%' IDENTIFIED BY '密碼';
# 套用變更
FLUSH PRIVILEGES;
# 確認用戶權限及列表
SELECT User, Host, Password FROM mysql.user;
# 查看資料庫儲存位置
SELECT @@datadir;
EXIT;

# 停止資料庫伺服器
systemctl stop mariadb
# 遷移複製資料庫數據
sudo rsync -a  /var/lib/mysql/ /mysql
# 配置新資料庫儲存路徑
sudo vi /etc/my.cnf


'''
[mysqld]
datadir=/mysql
socket=/mysql/mysql.sock
# append at the end
[client]
port=3306
socket=/mysql/mysql.sock
'''


# 允許通過 SELinux 防護
sudo semanage fcontext -a -t mysqld_db_t "/mysql(/.*)?"
sudo restorecon -R /mysql
# 套用配置
systemctl start mariadb
# 清除 DB 指令
> ~/.mysql_history

操作

# 用戶權限列表
SELECT User, Host Password FROM mysql.user;
# 使用某資料庫，mysql 為系統表
USE mysql;
# 變更用戶密碼
UPDATE user SET Password=PASSWORD("新密碼") WHERE User='用戶名';
# 創建權限
GRANT ALL PRIVILEGES ON 資料庫.表 TO '使用者'@'IP' IDENTIFIED BY '密碼';
# 移除權限
REVOKE ALL PRIVILEGES ON 資料庫.表 FROM '使用者'@'IP';
# 創建資料庫
CREATE DATABASE `資料庫名稱` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 套用變更
FLUSH PRIVILEGES;

備份復原

# 備份
mysqldump -u<用戶> -p<密碼> <資料庫名稱> > ~/Desktop/<資料庫名稱.sql>
mysqldump --user=<用戶> --password=<密碼> --all-databases > ~/Desktop/<匯入檔案或指令.sql>
# 復原
mysql --user=用戶 --password=密碼 < ~/Desktop/<匯入檔案或指令.sql>