MySQL

MySQL

https://blog.youkuaiyun.com/qq_44792624/article/details/107426590

1. 下载压缩包

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz

2. 解压

tar xvf mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz

3. 复制到指定目录

mv mysql-5.7.41-linux-glibc2.12-x86_64 /usr/local/mysql

4. 创建mysql用户和组

groupadd mysql

useradd -r -g mysql mysql -d /usr/local/mysql

passwd mysql

密码：111111

5. 修改目录权限

chown -R mysql:mysql /usr/local/mysql

chown -R mysql:mysql /data/mysql

6. 安装数据库

su mysql

/usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --initialize

exit

7. 复制mysql配置文件

cd /usr/local/mysql/support-files

cp my-default.cnf /etc/my.cnf

8. 添加系统服务

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

chkconfig mysql on

9. 添加环境变量

vim /etc/profile

在最下面添加

export MYSQL_HOME=/usr/local/mysql
export JAVA_HOME=/opt/jdk1.8.0_281
export PATH=$PATH:$JAVA_HOME/bin:$MYSQL_HOME/bin

保存退出后，执行生效代码

source /etc/profile

10. 配置my.cnf

vim /etc/my.cnf

[client]
port            = 10003
socket          = /data/mysql/mysql.sock

[mysql]
no-auto-rehash
max_allowed_packet = 128M
prompt                         = '(\u@\h) [\d]> '
default_character_set          = utf8mb4
#pager = "more"


[mysqld_multi]
mysqld      = /usr/local/mysql/bin/mysqld
mysqladmin  = /usr/local/mysql/bin/mysqladmin
user        = root

[mysqldump]
quick
max_allowed_packet          = 128M
#myisam_max_sort_file_size  = 10G

[myisamchk]
key_buffer_size            = 64M
sort_buffer_size           = 512k
read_buffer                = 2M
write_buffer               = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so


#[mysqld3306]
[mysqld]
#skip-grant-tables
port            = 10003
user            = mysql
socket          = /data/mysql/mysql.sock
basedir         = /usr/local/mysql  
datadir         = /data/mysql/mysql_data
tmpdir          = /data/mysql/mysql_tmp

character-set-server    = utf8mb4    
sysdate-is-now                
skip-name-resolve              
read_only              =0

open_files_limit        = 60000       
table_open_cache        = 4096
table_definition_cache  = 4096

max_connections         = 5000         
max_connect_errors      = 100000   
back_log  = 1000

wait_timeout  = 3000
interactive_timeout  = 3000


sort_buffer_size      = 32M
read_buffer_size  = 8M
read_rnd_buffer_size  = 16M
join_buffer_size = 32M
tmp_table_size = 512M
max_heap_table_size  = 512M
max_allowed_packet  = 128M
myisam_sort_buffer_size = 64M


key_buffer_size      = 1G
query_cache_type  = 0
query_cache_size    = 0         



eq_range_index_dive_limit= 2000
lower_case_table_names   = 1       

explicit_defaults_for_timestamp = 1  
# ====================== Logs Settings ================================     
log-error            = /data/mysql/mysql_log/logs/error.log
slow-query-log      
slow-query-log-file  = /data/mysql/mysql_log/logs/slow.log
long_query_time      = 3

#log_slow_slave_statements = 1

log_bin_trust_function_creators=1
log-bin         = /data/mysql/mysql_log/binlog/mysql-bin
log-bin-index   = /data/mysql/mysql_log/binlog/mysql-bin.index

sync_binlog        = 1
expire_logs_days   = 7
binlog_format     = ROW
binlog_cache_size  = 8M


# ===================== Replication settings =========================
server-id          = 74172
binlog_gtid_simple_recovery      = 1
gtid_mode                        = off
enforce-gtid-consistency         = 1

relay-log          = /data/mysql/mysql_log/relaylog/mysql-relay-bin
relay-log-index    = /data/mysql/mysql_log/relaylog/mysql-relay-bin.index
relay-log-purge    = 0
log-slave-updates
master_info_repository    = TABLE
relay_log_info_repository = TABLE
relay_log_recovery       = 1

# ====================== INNODB Specific Options ======================
innodb_data_home_dir             = /data/mysql/mysql_data               
innodb_data_file_path         = ibdata1:10M:autoextend      
innodb_buffer_pool_size        = 3G                         
innodb_log_buffer_size         = 64M 
innodb_log_group_home_dir       = /data/mysql/mysql_data
innodb_log_files_in_group       = 5                         
innodb_log_file_size               = 50m                           
innodb_fast_shutdown          = 1
innodb_force_recovery         = 0
innodb_file_per_table               = 1                             
innodb_lock_wait_timeout       = 100
innodb_thread_concurrency       = 64
innodb_flush_log_at_trx_commit       = 1
innodb_flush_method             = O_DIRECT
innodb_read_io_threads              = 12
innodb-write-io-threads      = 16
innodb_io_capacity       = 100
innodb_io_capacity_max       = 500
innodb_purge_threads      = 1
innodb_autoinc_lock_mode             = 2
innodb_buffer_pool_instances         = 8
innodb_sort_buffer_size       = 6M
innodb_max_dirty_pages_pct       = 75                       
transaction-isolation       = READ-COMMITTED                
# ======================  Undo Options ======================
innodb_undo_directory =/data/mysql/mysql_data
innodb_undo_logs = 128
innodb_undo_tablespaces = 4
innodb_undo_log_truncate = on
innodb_max_undo_log_size = 100m
innodb_purge_rseg_truncate_frequency = 128

# ======================  mysqld-5.7 ======================
log_timestamps                   = system
innodb_purge_rseg_truncate_frequency = 128
innodb_buffer_pool_dump_pct      = 40
innodb_undo_log_truncate         = on
innodb_max_undo_log_size         = 5M
slave_preserve_commit_order      = 1
show_compatibility_56            =on
slave-parallel-type              = LOGICAL_CLOCK
slave_parallel_workers          = 8

group_concat_max_len = 102400
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
event_scheduler=ON
[mysqldump]
quick
max_allowed_packet = 128M 

11. 启动和关闭mysql

service mysql start

service mysql stop

12. 创建用户授权数据库

CREATE USER 'test'@'%' IDENTIFIED BY 'test2022';
--创建用户

GRANT ALL PRIVILEGES ON xiyuefa.* TO 'test'@'%' IDENTIFIED BY 'test2022' WITH GRANT OPTION;
FLUSH PRIVILEGES;

13. 允许所有外部链接访问（可选）

GRANT ALL PRIVILEGES ON *.* TO ‘root’@‘%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

参考初始化密码修改：

1.开启跳过权限验证

vi /etc/my.cnf

[mysqld]
skip-grant-tables
/etc/init.d/mysql restart

2.重置密码

use mysql;
update user set authentication_string=password('123456') where user='root' and host='localhost';
flush privileges;

CREATE USER 'test'@'%' IDENTIFIED BY 'test*1qaz*.xsw2';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' IDENTIFIED BY 'test*1qaz*.xsw2' WITH GRANT OPTION;
FLUSH PRIVILEGES; 

 /etc/init.d/mysql restart
vi /etc/my.cnf
[mysqld]
skip-grant-tables    #delete
/etc/init.d/mysql restart

Mysql5.8 外网访问需要先创建用户，不用root

CREATE USER 'test'@'%' IDENTIFIED BY 'test.1234';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

修改密码：ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'test.1234QSX';

bash install_mysql_from_rpm.sh -v 5.7.35

执行安装5.7.35

vi /etc/selinux/config

SELINUX=disabled

msyql重启命令：systemctl restart mysqld