mysql初始化流程

文章来自于：globlogs

– 环境初始化

mkdir /data/soft

scp mysql80-community-release-el7-3.noarch.rpm

cd /data/
yum remove mariadb* -y

rpm -ivh mysql80-community-release-el7-3.noarch.rpm
yum module disable mysql
yum install mysql -y
yum install mysql-community-server -y

systemctl stop mysqld
systemctl disable mysqld

mkdir /data/mysql/3309 -p

–编写3309端口的配置文件

cat > /data/mysql/3309/my3309.cnf << eof 

[mysql] prompt = [\u@\p][\d]>_ no-auto-rehash

[mysqld_safe]
malloc-lib=tcmalloc

[mysqldump]
single-transaction

[mysqld]
#basic settings#
user = mysql
autocommit = 1
server-id = 23309
port=3309
mysqlx_port=13309
character_set_server=utf8mb4
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/mysql3309.sock
mysqlx_socket=/data/mysql/3309/mysqlx13309.sock
pid-file=/data/mysql/3309/data/mysqld.pid
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
max_allowed_packet = 64M
event_scheduler = 1
skip-slave-start

#connection#
interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 1800
skip_name_resolve = 1
max_connections = 6000
#max_user_connections = 1024
max_connect_errors = 10000

#table cache performance settings
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 64

#session memory settings #
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
tmp_table_size = 64M
join_buffer_size = 128M
thread_cache_size = 64

#log settings#
log_error = error.log
log_bin = binlog
log_error_verbosity = 2
general_log_file = general.log
general_log = off
slow_query_log = 1
slow_query_log_file = slow.log
#log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
#log_throttle_queries_not_using_indexes = 10
long_query_time = 2
lower_case_table_names=1
#min_examined_row_limit = 100
log-bin-trust-function-creators = 1
log-slave-updates = 1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

#innodb settings#
innodb_page_size = 16384
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 4g
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 50
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_purge_threads = 4
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 128M
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size=1G
innodb_open_files=4096

#replication settings#
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = ROW
binlog_rows_query_log_events = 1
relay_log = relay.log
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'

#replication settings mysql8.0#
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
slave_preserve_commit_order = 1
slave_transaction_retries = 128
binlog_gtid_simple_recovery = 1
log_timestamps = system

#semi sync replication settings#
plugin-load = "semisync_master.so;semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_master_timeout = 3000
loose_rpl_semi_sync_slave_enabled = 1

#password plugin#
default_authentication_plugin = mysql_native_password

#perforamnce_schema settings
performance-schema-instrument='memory/%=COUNTED'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
max_allowed_packet=1024M

eof

vi /data/mysql/3309/my3309.cnf

–授权文件所属账户

chown mysql:mysql /data/mysql/3309 -R

–初始化数据哭

mysqld --defaults-file=/data/mysql/3309/my3309.cnf --initialize & 
mysqld --defaults-file=/data/mysql/3309/my3309.cnf --user=mysql &

---

–编写数据启动脚本

cat >> /root/start_mysql.sh <<eof
#!/bash/bin
#start db
mysqld --defaults-file=/data/mysql/3309/my3309.cnf --user=mysql &

eof

–编写my.cnf配置文件

cat >> /etc/my.cnf << eof
[mysql]
prompt = [\u@\p][\d]>_
no-auto-rehash

eof

–获取数据库默认密码

cat /data/mysql/3309/data/error.log |grep pass

–登陆到数据库修改root账号的密码

mysql -uroot -p -S/data/mysql/3309/mysql3309.sock

alter user root@'localhost' identified by 'SaSd5kGsOn1weRkF';
exit

–制作免密码登陆脚本

mysql_config_editor set --login-path=my3309 --user=root  --socket=/data/mysql/3309/mysql3309.sock  --password

cat >> /root/.bashrc << eof
alias 'mysql3309.in'='mysql --login-path=my3309'
eof
source /root/.bashrc

–创建同步账号和密码

create user repl@% identified by 're123pl654';
GRANT REPLICATION SLAVE ON . TO repl@%;
flush privileges;

–在从库中配置主库信息

CHANGE MASTER TO
MASTER_HOST='172.27.250.223',
MASTER_PORT=3309,MASTER_USER='repl',MASTER_PASSWORD='re123pl654',MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=1117
;

–编写自动备份脚本

-- bakcup

mysql -e "show databases;" -uroot -p| grep -Ev "Database|information_schema|mysql|test" | xargs mysqldump -uroot -p --databases > mysql_dump.sql

--
#!/bin/bash
DATE=date +%Y-%m-%d-%H
echo $DATE
DATE2=date +%Y-%m-%d-%H -d "-10 hours"

BACKUP_PATH=/data/backup
REMOTE_HOST=52.231.37.242
#REMOTE_HOST=101.32.201.129

#/usr/bin/mysqldump --login-path=my3306 -R --opt $DATABASE --set-gtid-purged=OFF --skip-lock-tables | gzip > $BACKUP_PATH/$DATABASE$DATE.sql.gz
/usr/bin/mysql --login-path=my3309 -e "show databases;" | grep -Ev "Database|information_schema|mysql|sys|performance_schema" | xargs /usr/bin/mysqldump --login-path=my3309 --databases |gzip > $BACKUP_PATH/DATABASE$DATE.sql.gz
echo '数据本地备份成功'
#rsync -P --rsh=ssh $BACKUP_PATH/chzx_chat$DATE.sql.gz root@$REMOTE_HOST:$BACKUP_PATH
scp -P22 $BACKUP_PATH/DATABASE$DATE.sql.gz vpnuser@$REMOTE_HOST:$BACKUP_PATH/newqf
echo '数据远程发送成功'
ssh vpnuser@$REMOTE_HOST rm -f $BACKUP_PATH/newqf/DATABASE$DATE2.sql.gz
echo '远程归档数据删除成功'

rm -rf $BACKUP_PATH/DATABASE$DATE.sql.gz

–创建账号及授权数据库

create user chat_resource_user@% identified by 'vWd9OQpijsbbRvHS';
GRANT USAGE ON . TO chat_resource_user@%GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON chat_resource.* TO chat_resource_user@%;

mysql初始化流程.txt 7218