mysql初始化流程_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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

–编写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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

–授权文件所属账户

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

–初始化数据哭

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

---

–编写数据启动脚本

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

eof
1
2
3
4
5
6

–编写my.cnf配置文件

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

eof
1
2
3
4
5
6

–获取数据库默认密码

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

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

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

alter user root@'localhost' identified by 'SaSd5kGsOn1weRkF';
exit
1
2
3
4

–制作免密码登陆脚本

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
1
2
3
4
5
6

–创建同步账号和密码

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

–在从库中配置主库信息

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
;
1
2
3
4
5

–编写自动备份脚本

-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

–创建账号及授权数据库

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@%;
1
2

mysql初始化流程.txt 7218