AWS EC2上搭建MySQL8.0.34

安装MySQL8.0.34

• 初始化结束后密码位置：error日志中

1. dump之前需检查磁盘空间，需至少预留mysql数据2倍的空间

df -h

需要导出的库和表

库：dw_dbproxy_config
    表：server_info

安装脚本

#!/bin/sh

#安装8.0版本
#########################

mysql8_install(){
parentDir=/data/mysql8/3308
mkdir -p $parentDir
cd $parentDir
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.34-linux-glibc2.17-x86_64.tar.gz
tar zxvf mysql-8.0.34-linux-glibc2.17-x86_64.tar.gz
mkdir -p $parentDir/data
mv $parentDir/mysql-8.0.34-linux-glibc2.17-x86_64 mysql
chown -R mysql:mysql $parentDir

#配置文件
cat >> $parentDir/my_3308.cnf << EOF
[client]
port=3308
socket=$parentDir/mysql.sock
character_set_client=utf8mb4
character_set_results=utf8mb4
#init_connect=utf8mb4

[mysql]
default-character-set=utf8mb4
character_set_connection=utf8mb4
character_set_client=utf8mb4
character_set_results=utf8mb4
#init_connect=utf8mb4

[mysqld]
########basic settings########
sql_mode="NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"
default_authentication_plugin=mysql_native_password
#validate_password.length=4;    
#validate_password.mixed_case_count=0;
#validate_password.number_count =0;
#validate_password.special_char_count=0;
#validate_password.policy=LOW;
#init_connect=utf8mb4

innodb_page_size = 65535K
innodb_strict_mode = off
innodb_file_per_table=1
innodb_log_file_size = 2G
innodb_log_buffer_size=512M

server-id=3308
port=3308
user = mysql
basedir=$parentDir/mysql
datadir=$parentDir/data
socket=$parentDir/mysql.sock
pid-file=$parentDir/mysql.pid
character-set-server=utf8mb4

#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
sort_buffer_size = 16M
join_buffer_size = 16M
tmp_table_size = 72M
max_allowed_packet = 1G
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M

table_open_cache=2000
thread_cache_size=768

########log settings########
general_log_file=$parentDir/mysqldb-general.err
slow_query_log = ON
slow_query_log_file=$parentDir/mysqldb-query.err
long_query_time=1
log-error=$parentDir/mysqldb-error.err

expire_logs_days = 1
min_examined_row_limit = 100

########innodb settings########
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 1
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT

#innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
#innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 128M

innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=2
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_checksum_algorithm=0

EOF

#初始化mysql
/data/dba_bak/8/mysql8/bin/mysqld --defaults-file=$parentDir/my_3308.cnf --basedir=$parentDir/mysql --datadir=$parentDir/data --user=mysql --initialize
sleep 5;
echo "-----------初始化成功------------"

#启动mysql
$parentDir/mysql/bin/mysqld_safe --defaults-file=$parentDir/my_3308.cnf --basedir=$parentDir/mysql --datadir=$parentDir/data --user=mysql &
sleep 10;
echo "-----------启动成功------------"

#登录密码
tmp_password=`more /data/mysql_3308/mysqldb-error.err | grep -i "A temporary password"|awk '{print $NF}'`

#添加账号
mysql --connect-expired-password -u root -p${tmp_password} --socket=$parentDir/mysql.sock --port=3308 -e "CREATE USER 'root'@'%' IDENTIFIED BY '12345678';GRANT all ON *.* TO 'root'@'%' WITH GRANT OPTION;ALTER USER root@'%' IDENTIFIED WITH mysql_native_password BY '12345678';flush privileges;" 

#添加系统自启动
cat >> /etc/rc.local << EOF
#by dba
$parentDir/mysql/bin/mysqld_safe --defaults-file=$parentDir/my_3308.cnf --basedir=$parentDir/mysql --datadir=$parentDir/data --user=mysql &
EOF
chmod +x /etc/rc.local 

echo "安装完成--------------"
echo "添加自启动完成--------------"

}

main(){

    mysql8_install
}

main

安装并行dump工具

yum -y install epel-release 
yum -y install parallel

parallel  -h 查看用法

并行dump脚本

/data/dba/yanhao/shell/ip_44.228.247.127/dump_coa_1.sh

密码强度问题解决

mysql8.0 创建用户密码要求为大小写字母和数字，因此需要设置降低强度。

https://blog.csdn.net/qq_18948359/article/details/106064712

https://www.cnblogs.com/kerrycode/p/13501292.html

安装组件

mysql> INSTALL COMPONENT 'file://component_validate_password';

修改密码强度

show variables like 'validate%';

set global validate_password.length=4;    
set global validate_password.mixed_case_count=0;
set global validate_password.number_count =0;
set global validate_password.special_char_count=0;
set validate_password.policy=LOW;
## 可直接修改到配置文件中，持久化配置
## 在[mysqld]中添加

修改密码相关

• 修改root用户密码

alter user 'root'@'localhost' identified with mysql_native_password by '12345678';
flush privileges;

• 将root用户密码设置为空

update user set authentication_string = '' where user = 'root';

CREATE USER 'hellofarm'@'%' IDENTIFIED BY 'halfquestfarm4321';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `hellofarm`@`%`;

flush privileges;

• 删除用户

drop user xxx;

• 快捷键设置

  • 软连接

  ln -s [目标文件绝对路径] [软连接名称]
  

  • Alias

  vim /etc/profile
  
  alias mysql='mysql -h127.0.0.1 -P3306 -uroot -p12345678'
  

mydump和myloader的安装和使用

https://www.cnblogs.com/li-954172807/articles/7810991.html

parallel安装与使用

https://www.jianshu.com/p/2a1a4de0d8b1

Validate password组件与插件

• 修改密码强度要求

## 组件
set global validate_password.policy=LOW;
## 插件
set global validate_password_policy=LOW;

• 插件没有对应系统变量，组件才有

• 卸载

## 卸载插件
UNINSTALL PLUGIN  validate_password;
## 卸载组件
UNINSTALL COMPONENT 'file://component_validate_password';

报错及解决

• error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

sudo yum install -y libaio