实战！MySQL主从复制一键搭建脚本分享

一、环境说明：

本次准备了两台机器搭建mysql主从复制。只需要安装好操作系统，挂载系统镜像和上传所需的安装包和脚本即可。另外，脚本部分位置需要修改IP地址。

网盘地址：

1. 链接: https://pan.baidu.com/s/1PUcGP0qSBphCfxCsFFhFSg
   
2. 提取码: pvdv
   

上传安装包和脚本至任意目录下,赋予执行权限，执行mysql_ms_install.sh即可。整个过程中只需要输入数据库的版本号即可。

1. [root@db1 ~]#sh -x mysql_ms_install.sh
   

二、安装过程部分截图：

三、脚本内容：

1. [root@db1 ~]#cat mysql_ms_install.sh
   
2. #!/bin/bash
   
3. echo "-----------------------------开始MYSQL主从搭建--------------------------------------"
   
4. start_time=$(date +%s)
   
5. #关闭Selinux
   
6. setenforce 0
   
7. sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
   
8. #关闭防火墙
   
9. systemctl stop firewalld
   
10. systemctl disable firewalld
    
11. systemctl status firewalld
    
12. #配置yum源
    
13. cd /etc/yum.repos.d/
    
14. rm -rf ./*
    
15. cat >> /etc/yum.repos.d/centos.repo <<-EOF
    
16. [centos]
    
17. name=oracle
    
18. baseurl=file:///mnt
    
19. enabled=1
    
20. gpgcheck=0
    
21. EOF
    
22. cd
    
23. mount /dev/sr0 /mnt    
    
24. yum clean all|wc -l
    
25. yum makecache
    
26. yum install expect* -y
    
27. yum install wget -y
    
28. #yum install gcc build-essential -y
    
29. #tar -xvf sshpass.tar.gz
    
30. #cd /root/sshpass-1.10
    
31. #./configure
    
32. #make
    
33. #make install
    
34.    os_name=${node_name}
    
35.    if [[ "$os_name" =~ ^localhost ]];then
    
36.        echo "ERROR: hostname: $os_name is invalid,pls check"
    
37.        exit -1
    
38.    else
    
39.        echo "hostname check passed"
    
40.        hostnamectl set-hostname ${node_name}db1
    
41.    fi
    
42. # 变量定义
    
43. groupadd mysql
    
44. useradd -r -g mysql mysql
    
45. # 定义变量
    
46. REMOTE_USER="root"
    
47. REMOTE_HOST="192.168.59.250"
    
48. PASSWORD="123123"
    
49. # 本地生成SSH密钥对（如果不存在）
    
50. if [ ! -f ~/.ssh/id_rsa ]; then
    
51.    ssh-keygen -t rsa -b 2048 -N "" -f ~/.ssh/id_rsa
    
52. fi
    
53. # 使用Expect脚本将公钥复制到远程主机
    
54. /usr/bin/expect <<EOF
    
55. set timeout 10
    
56. # 启动ssh-copy-id命令
    
57. spawn ssh-copy-id $REMOTE_USER@$REMOTE_HOST
    
58. # 处理首次连接提示的 "yes/no" 和密码输入
    
59. expect {
    
60.    "yes/no" { send "yes\r"; exp_continue }
    
61.    "password:" { send "$PASSWORD\r" }
    
62. }
    
63. expect eof
    
64. EOF
    
65. # 验证是否成功
    
66. ssh $REMOTE_USER@$REMOTE_HOST "echo '免密登录设置成功'"
    
67. REMOTE_COMMANDS=$(cat << EOF
    
68. echo "hostname:"
    
69. os_name=${node_name}
    
70.    if [[ "$os_name" =~ ^localhost ]];then
    
71.        echo "ERROR: hostname: $os_name is invalid,pls check"
    
72.        exit -1
    
73.    else
    
74.        echo " hostname check passed"
    
75.        hostnamectl set-hostname ${node_name}db2
    
76.    fi
    
77. EOF
    
78. )
    
79. ssh $REMOTE_USER@$REMOTE_HOST "$REMOTE_COMMANDS"
    
80. #添加主机名与IP对应记录
    
81. public_ip1=$(hostname -I| grep -o -e '[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}' |head -n 1)
    
82. node_name1=$(hostname)
    
83. sleep 3
    
84. public_ip2=$(ssh $REMOTE_USER@$REMOTE_HOST "hostname -I | grep -o -e '[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}.[0-9]\{1,3\}' | head -n 1")
    
85. node_name2=$(ssh $REMOTE_USER@$REMOTE_HOST "hostname")
    
86. sleep 3
    
87. echo -e "${public_ip1} ${node_name1}\n${public_ip2} ${node_name2}" >> /etc/hosts
    
88. cat /etc/hosts
    
89. cd ~
    
90. echo "-----------------------------开始MYSQL MASTER安装--------------------------------------"
    
91. start_time=$(date +%s)
    
92. MYSQL_ROOT_PASSWORD="123456"
    
93. REPLICA_USER="repl"
    
94. REPLICA_PASSWORD="repl"
    
95. echo -e "\e[31m***************一键安装mysql任何版本数据库******************\e[0m"
    
96. echo -e "输入安装版本，如\e[31m8.0.27\e[0m"
    
97. read version
    
98. find / -name mysql | xargs rm -rf
    
99. port=$(netstat -antup|grep mysql|wc -l)
    
100. if [ $port != 0 ]
     
101. then echo "mysql进程存在,请先杀掉进程"
     
102. ps -ef |grep mysqld
     
103. exit 1
     
104. fi
     
105. echo "-----------------创建所需目录及用户并上传安装包----------------------------"
     
106. # 获取当前所在目录位置
     
107. current_dir=$(pwd)
     
108. echo "当前所在目录位置: $current_dir"
     
109. # 目标路径
     
110. target_dir="/opt"
     
111. # 检查目标路径是否存在，如果不存在则创建
     
112. if [ ! -d "$target_dir" ]; then
     
113.    mkdir -p "$target_dir"
     
114.    echo "已创建目录: $target_dir"
     
115. fi
     
116. # 移动当前目录下的所有文件到目标路径
     
117. mv $current_dir/* $target_dir
     
118. cp mysql_install.sh /opt
     
119. echo "已将当前目录下所有文件移动至 $target_dir"
     
120. mkdir -p  /data/mysql
     
121. groupadd mysql
     
122. useradd -r -g mysql mysql
     
123. cd /opt/
     
124. tar -xvf mysql-$version-linux-glibc2.12-x86_64.tar.xz
     
125. mv mysql-$version-linux-glibc2.12-x86_64/  /usr/local/
     
126. cd /usr/local/
     
127. mv mysql-$version-linux-glibc2.12-x86_64/ mysql
     
128. chown -R mysql.mysql /usr/local/mysql/
     
129. echo "-----------------------------卸载原有的mysql组件--------------------------"
     
130. yum list installed | grep mariadb
     
131. yum -y remove mariadb* || true
     
132. chown mysql:mysql -R /data/mysql
     
133. touch /etc/my.cnf
     
134. chmod 644 /etc/my.cnf
     
135. MYSQL_ROOT_PASSWORD=123456
     
136. cat <<EOF >/etc/my.cnf
     
137. [client]
     
138. port = 3306
     
139. socket = /tmp/mysql.sock
     
140. 
     
141. [mysql]
     
142. default-character-set = utf8mb4
     
143. 
     
144. [mysqld]
     
145. user = mysql
     
146. port = 3306
     
147. basedir = /usr/local/mysql
     
148. datadir = /data/mysql
     
149. log-error=/data/mysql/mysql.err
     
150. socket = /tmp/mysql.sock
     
151. pid-file = /data/mysql/mysql.pid
     
152. innodb_buffer_pool_size = 8G      # 配置为内存的75%
     
153. innodb_buffer_pool_instances = 8   # 配置buffer_pool为8个
     
154. innodb_log_file_size = 2G          # 调整日志文件大小
     
155. innodb_log_buffer_size = 128M      # InnoDB用于写入磁盘日志文件的缓冲区大小
     
156. innodb_flush_log_at_trx_commit = 1 # 保证数据安全
     
157. innodb_file_per_table = 1          # 每张表独立表空间
     
158. innodb_io_capacity = 2000          # I/O操作数
     
159. innodb_read_io_threads = 4         # 读I/O线程
     
160. innodb_write_io_threads = 4        # 写I/O线程
     
161. innodb_flush_method = O_DIRECT     # 防止双写
     
162. default_storage_engine = InnoDB    # 存储引擎默认InnoDB
     
163. max_connections = 500              # 最大连接数
     
164. max_connect_errors = 1000          # 最大错误连接次数
     
165. skip_name_resolve = 1              # 只能用IP地址检查客户端的登录
     
166. server-id = 1                       # 主库ID
     
167. gtid_mode = ON                      # 启用GTID
     
168. enforce-gtid-consistency = ON       # 强制GTID一致性
     
169. log-bin = mysql-bin                 # 启用二进制日志
     
170. binlog_format = ROW                 # 二进制日志格式
     
171. binlog_row_image = FULL             # 行级日志模式
     
172. master_info_repository = TABLE      # 主库信息表存储
     
173. relay_log_info_repository = TABLE   # 中继日志信息表存储
     
174. sync-binlog = 1                     # 保证数据安全
     
175. log-slave-updates = 1               # 允许从库更新记录到binlog
     
176. binlog_expire_logs_seconds = 604800 # binlog保留时间
     
177. slave_parallel_workers = 4          # 从库并行复制线程数
     
178. slave_parallel_type = LOGICAL_CLOCK # 并行复制类型
     
179. table_open_cache = 4096             # 程打开的表的数量            
     
180. table_definition_cache = 2048       # 可以存储在定义缓存中的表定义数量
     
181. tmp_table_size = 128M               # 内部内存临时表大小
     
182. max_heap_table_size = 256M          # 内部内存临时表的最大值
     
183. thread_cache_size = 128             # 连接使用缓存线程
     
184. open_files_limit = 65535            # mysqld进程能使用的最大文件描述(FD)符数量
     
185. EOF
     
186. echo "-----------------------------------初始化-----------------------------------"
     
187. cd /usr/local/mysql/bin
     
188. ./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize
     
189. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
     
190. path=$(grep 'basedir' /etc/profile|wc -l)
     
191. if [ $path !=  0 ]
     
192.  then
     
193.    echo -e "\e[31m MYSQL_HOME路径存在\e[0m"
     
194.  else
     
195.    echo "export basedir=/usr/local/mysql/bin" >> /etc/profile
     
196.    echo "export PATH=\$PATH:\$basedir"        >> /etc/profile
     
197.    source /etc/profile
     
198. fi
     
199. echo "---------------------------------启动MYSQL服务---------------------------------------"
     
200. service mysql start
     
201. echo 'export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib'>>/etc/profile
     
202. sleep 3
     
203. source /etc/profile
     
204. cat /data/mysql/mysql.err|grep password
     
205. chkconfig --add mysql
     
206. chkconfig mysql on
     
207. chkconfig --list mysql
     
208. echo "-----------------------------恭喜！MYSQL 主库安装成功--------------------------------------"
     
209. end_time=$(date +%s)
     
210. execution_time=$((end_time - start_time))
     
211. echo "脚本执行时间：${execution_time} 秒"
     
212. MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.err`
     
213. mysqladmin  -uroot -p${MYSQL_OLDPASSWORD} password ${MYSQL_ROOT_PASSWORD}
     
214. mysql -uroot -p123456 -e "update mysql.user set host ='%' where user ='root';"
     
215. mysql -uroot -p123456 -e "flush privileges;"
     
216. mysql -uroot -p123456 << EOF
     
217. exit
     
218. EOF
     
219. sleep 10
     
220. #EOF
     
221. #)
     
222. #ssh $REMOTE_USER@$REMOTE_HOST "$REMOTE_COMMANDS1"
     
223. echo "-----------------------------开始安装MYSQL SLAVE--------------------------------------"
     
224. source /etc/profile
     
225. scp /opt/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz mysql_install.sh root@192.168.59.250:/opt
     
226. scp /opt/mysql_install.sh root@192.168.59.250:/opt/
     
227. ssh -tt -p 22 root@192.168.59.250 "source /opt/mysql_install.sh"
     
228. #REMOTE_USER="root"
     
229. ln -fs /usr/local/mysql/bin/mysql /usr/bin/
     
230. REMOTE_HOST="192.168.59.250"
     
231. #REMOTE_SCRIPT_PATH="/opt/mysql_install.sh"
     
232. #ssh 192.168.59.250 "source /opt/mysql_install.sh"
     
233. ssh =$REMOTE_HOST <<EOF
     
234. if [ $? -eq 0 ]; then
     
235.    echo "MySQL 安装已成功完成."
     
236. else
     
237.    echo "MySQL 安装失败，请检查日志或手动排查问题."
     
238. fi
     
239. # 添加其他命令，如清理临时文件或重启服务
     
240. # 例如： rm /tmp/temp_file
     
241. exit
     
242. EOF
     
243. sleep 10
     
244. /usr/bin/expect <<EOF
     
245. set timeout 60
     
246. # 启动SSH命令
     
247. spawn ssh root@192.168.59.249
     
248. # 处理首次连接提示的 "yes/no" 和密码输入
     
249. expect {
     
250.    "Are you sure you want to continue connecting (yes/no)?" { send "yes\r"; exp_continue }
     
251.    "password:" { send "123123\r"; exp_continue }
     
252.    eof
     
253. }
     
254. EOF
     
255. sleep 10
     
256. source /etc/profile
     
257. ln -fs /usr/local/mysql/bin/mysql /usr/bin/
     
258. MASTER_IP=192.168.59.249
     
259. SLAVE_IP=192.168.59.250
     
260. # 配置主服务器
     
261. function configure_master {
     
262. REMOTE_USER="root"
     
263. REMOTE_HOST="192.168.59.249"
     
264. MYSQL_ROOT_PASSWORD="123456"
     
265. REPLICA_USER="repl"
     
266. REPLICA_PASSWORD="repl"
     
267. SSH_PASSWORD="123123"
     
268. /usr/bin/expect <<EOF
     
269. set timeout -1
     
270. set REMOTE_USER "root"
     
271. set REMOTE_HOST "192.168.59.249"
     
272. set MYSQL_ROOT_PASSWORD "123456"
     
273. set REPLICA_USER "repl1"
     
274. set REPLICA_PASSWORD "repl"
     
275. set SSH_PASSWORD "123123"
     
276. spawn mysql -u$REMOTE_USER -p$MYSQL_ROOT_PASSWORD -h$REMOTE_HOST -e "DROP USER IF EXISTS '$REPLICA_USER'@'%'; CREATE USER '$REPLICA_USER'@'%' IDENTIFIED BY '$REPLICA_PASSWORD'; USE mysql; GRANT REPLICATION SLAVE ON *.* TO '$REPLICA_USER'@'%'; FLUSH PRIVILEGES; SHOW MASTER STATUS;"
     
277. set output ""
     
278. expect {
     
279.    "*password:" {
     
280.        send "$MYSQL_ROOT_PASSWORD\r"
     
281.        exp_continue
     
282.    }
     
283.    eof {
     
284.        append output $expect_out(buffer)
     
285.    }
     
286. }
     
287. puts "Complete Output:\n$output\n"
     
288. set master_status [split $output "\n"]
     
289. puts "DEBUG: Master Status List:\n$master_status\n"  ; # 添加调试信息，查看分割后的列表
     
290. set file ""
     
291. set position ""
     
292. foreach line $master_status {
     
293.    if {[regexp {\| (\S+) \| (\d+) \|} $line -> file_temp position_temp]} {
     
294.        set file $file_temp
     
295.        set position $position_temp
     
296.        break
     
297.    }
     
298. }
     
299. puts "FILE: $file"
     
300. puts "POSITION: $position"
     
301. EOF
     
302. REMOTE_USER="root"
     
303. REMOTE_HOST="192.168.59.249"
     
304. MYSQL_ROOT_PASSWORD="123456"
     
305. OUTPUT_FILE="/opt/output.txt"
     
306. output=$(mysql -u"$REMOTE_USER" -p"$MYSQL_ROOT_PASSWORD" -h"$REMOTE_HOST" -e "SHOW MASTER STATUS\G")
     
307. echo "$output" > "$OUTPUT_FILE"
     
308. file=$(echo "$output" | grep "File:" | awk '{print $2}')
     
309. position=$(echo "$output" | grep "Position:" | awk '{print $2}')
     
310. echo "FILE: $file"
     
311. echo "POSITION: $position"
     
312. scp /opt/output.txt 192.168.59.250:/opt
     
313. file=$(cat "/opt/output.txt" | grep "File:" | awk '{print $2}')
     
314. position=$(cat "/opt/output.txt" | grep "Position:" | awk '{print $2}')
     
315. export FILE="$file"
     
316. export POSITION="$position"
     
317. }
     
318. # 配置从服务器
     
319. function configure_slave {
     
320. file=$(cat "/opt/output.txt" | grep "File:" | awk '{print $2}')
     
321. position=$(cat "/opt/output.txt" | grep "Position:" | awk '{print $2}')
     
322. export FILE="$file"
     
323. export POSITION="$position"
     
324. REMOTE_USER="root"
     
325. REMOTE_HOST="192.168.59.250"
     
326. MYSQL_ROOT_PASSWORD="123456"
     
327. REPLICA_USER="repl"
     
328. REPLICA_PASSWORD="repl"
     
329. SSH_PASSWORD="123123"
     
330. MASTER_HOST="192.168.59.249"
     
331. /usr/bin/expect <<EOF
     
332. set timeout -1
     
333. set REMOTE_USER "root"
     
334. set REMOTE_HOST "192.168.59.250"
     
335. set MASTER_IP "192.168.59.249"
     
336. set MYSQL_ROOT_PASSWORD "123456"
     
337. set REPLICA_USER "repl"
     
338. set REPLICA_PASSWORD "repl"
     
339. set SSH_PASSWORD "123123"
     
340. spawn mysql -u$REMOTE_USER -p$MYSQL_ROOT_PASSWORD -h192.168.59.250 -e "CHANGE MASTER TO MASTER_HOST='192.168.59.249', MASTER_USER='$REPLICA_USER', MASTER_PASSWORD='$REPLICA_PASSWORD', MASTER_LOG_FILE='$FILE', MASTER_LOG_POS=$POSITION;CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;START SLAVE;"
     
341. expect {
     
342.    "*password:" {
     
343.        send "$MYSQL_ROOT_PASSWORD\r"
     
344.        exp_continue
     
345.    }
     
346.    eof
     
347. }
     
348. #puts "\nSlave Status:\n$slave_output"
     
349. set show_slave_status_cmd "mysql -u$REMOTE_USER -p$MYSQL_ROOT_PASSWORD -h$REMOTE_HOST -e \"SHOW SLAVE STATUS\\\\G\""
     
350. spawn sh -c $show_slave_status_cmd
     
351. expect {
     
352.    "*password:" {
     
353.        send "$MYSQL_ROOT_PASSWORD\r"
     
354.        exp_continue
     
355.    }
     
356.    eof {
     
357.        set slave_output $expect_out(buffer)
     
358.        puts "SHOW SLAVE STATUS completed."
     
359.    }
     
360. }
     
361. puts "\nSlave Status:\n$slave_output"
     
362. EOF
     
363. configure_master
     
364. configure_slave
     

此脚本为mysql从库安装脚本：

1. cat mysql_install.sh
   
2. #!/bin/bash
   
3. echo "-----------------------------开始MYSQL SLAVE安装--------------------------------------"
   
4. #关闭Selinux
   
5. setenforce 0
   
6. sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
   
7. #关闭防火墙
   
8. systemctl stop firewalld
   
9. systemctl disable firewalld
   
10. systemctl status firewalld
    
11. #配置yum源
    
12. cd /etc/yum.repos.d/
    
13. rm -rf ./*
    
14. cat >> /etc/yum.repos.d/centos.repo <<-EOF
    
15. [centos]
    
16. name=oracle
    
17. baseurl=file:///mnt
    
18. enabled=1
    
19. gpgcheck=0
    
20. EOF
    
21. cd
    
22. mount /dev/sr0 /mnt    
    
23. yum clean all|wc -l
    
24. yum makecache
    
25. yum install expect* telent* -y
    
26. start_time=$(date +%s)
    
27. echo -e "\e[31m***************一键安装mysql任何版本数据库******************\e[0m"
    
28. echo -e "输入安装版本，如\e[31m8.0.27\e[0m"
    
29. read version
    
30. find / -name mysql | xargs rm -rf
    
31. port=$(netstat -antup|grep mysql|wc -l)
    
32. if [ $port != 0 ]
    
33. then echo "mysql进程存在,请先杀掉进程"
    
34. ps -ef |grep mysqld
    
35. exit 1
    
36. fi
    
37. echo "-----------------创建所需目录及用户并上传安装包----------------------------"
    
38. # 获取当前所在目录位置
    
39. current_dir=$(pwd)
    
40. echo "当前所在目录位置: $current_dir"
    
41. # 目标路径
    
42. target_dir="/opt"
    
43. # 检查目标路径是否存在，如果不存在则创建
    
44. if [ ! -d "$target_dir" ]; then
    
45.    mkdir -p "$target_dir"
    
46.    echo "已创建目录: $target_dir"
    
47. fi
    
48. # 移动当前目录下的所有文件到目标路径
    
49. mv $current_dir/* $target_dir
    
50. echo "已将当前目录下所有文件移动至 $target_dir"
    
51. mkdir -p  /data/mysql
    
52. groupadd mysql
    
53. useradd -r -g mysql mysql
    
54. cd /opt/
    
55. tar -xvf mysql-$version-linux-glibc2.12-x86_64.tar.xz
    
56. mv mysql-$version-linux-glibc2.12-x86_64/  /usr/local/
    
57. cd /usr/local/
    
58. mv mysql-$version-linux-glibc2.12-x86_64/ mysql
    
59. chown -R mysql.mysql /usr/local/mysql/
    
60. echo "-----------------------------卸载原有的mysql组件--------------------------"
    
61. yum list installed | grep mariadb
    
62. yum remove mariadb* -y || true
    
63. chown mysql:mysql -R /data/mysql
    
64. touch /etc/my.cnf
    
65. chmod 644 /etc/my.cnf
    
66. MYSQL_ROOT_PASSWORD=123456
    
67. cat <<EOF >/etc/my.cnf
    
68. [client]
    
69. port = 3306
    
70. socket = /tmp/mysql.sock
    
71. [mysql]
    
72. default-character-set = utf8mb4
    
73. [mysqld]
    
74. user = mysql
    
75. port = 3306
    
76. basedir = /usr/local/mysql
    
77. datadir = /data/mysql
    
78. log-error=/data/mysql/mysql.err
    
79. socket = /tmp/mysql.sock
    
80. pid-file = /data/mysql/mysql.pid
    
81. 
    
82. # 性能优化
    
83. innodb_buffer_pool_size = 8G      # 配置为内存的75%
    
84. innodb_buffer_pool_instances = 8   # 配置buffer_pool为8个
    
85. innodb_log_file_size = 2G          # 调整日志文件大小
    
86. innodb_log_buffer_size = 128M      # InnoDB用于写入磁盘日志文件的缓冲区大小
    
87. innodb_flush_log_at_trx_commit = 1 # 保证数据安全
    
88. innodb_file_per_table = 1          # 每张表独立表空间
    
89. innodb_io_capacity = 2000          # I/O操作数
    
90. innodb_read_io_threads = 4         # 读I/O线程
    
91. innodb_write_io_threads = 4        # 写I/O线程
    
92. innodb_flush_method = O_DIRECT     # 防止双写
    
93. default_storage_engine = InnoDB    # 存储引擎默认InnoDB
    
94. 
    
95. # 连接优化
    
96. max_connections = 500              # 最大连接数
    
97. max_connect_errors = 1000          # 最大错误连接次数
    
98. skip_name_resolve = 1              # 只能用IP地址检查客户端的登录
    
99. 
    
100. # 复制配置
     
101. server-id = 2                      # 从库ID
     
102. gtid_mode = ON                      # 启用GTID
     
103. enforce-gtid-consistency = ON       # 强制GTID一致性
     
104. log-bin = mysql-bin                 # 启用二进制日志
     
105. binlog_format = ROW                 # 二进制日志格式
     
106. binlog_row_image = FULL             # 行级日志模式
     
107. master_info_repository = TABLE      # 主库信息表存储
     
108. relay_log_info_repository = TABLE   # 中继日志信息表存储
     
109. sync-binlog = 1                     # 保证数据安全
     
110. log-slave-updates = 1               # 允许从库更新记录到binlog
     
111. binlog_expire_logs_seconds = 604800 # binlog保留时间
     
112. 
     
113. # 多线程复制
     
114. slave_parallel_workers = 4          # 从库并行复制线程数
     
115. slave_parallel_type = LOGICAL_CLOCK # 并行复制类型
     
116. 
     
117. # 缓存优化
     
118. table_open_cache = 4096             # 程打开的表的数量            
     
119. table_definition_cache = 2048       # 可以存储在定义缓存中的表定义数量
     
120. tmp_table_size = 128M               # 内部内存临时表大小
     
121. max_heap_table_size = 256M          # 内部内存临时表的最大值
     
122. thread_cache_size = 128             # 连接使用缓存线程
     
123. open_files_limit = 65535            # mysqld进程能使用的最大文件描述(FD)符数量
     
124. EOF
     
125. echo "-----------------------------------初始化-----------------------------------"
     
126. cd /usr/local/mysql/bin
     
127. ./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/ --user=mysql --initialize
     
128. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
     
129. path=$(grep 'basedir' /etc/profile|wc -l)
     
130. if [ $path !=  0 ]
     
131.  then
     
132.    echo -e "\e[31m MYSQL_HOME路径存在\e[0m"
     
133.  else
     
134.    echo "export basedir=/usr/local/mysql/bin" >> /etc/profile
     
135.    echo "export PATH=\$PATH:\$basedir"        >> /etc/profile
     
136.    source /etc/profile
     
137. fi
     
138. echo "---------------------------------启动MYSQL服务---------------------------------------"
     
139. service mysql start
     
140. echo 'export PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/lib'>>/etc/profile
     
141. sleep 3
     
142. source /etc/profile
     
143. cat /data/mysql/mysql.err|grep password
     
144. chkconfig --add mysql
     
145. chkconfig mysql on
     
146. chkconfig --list mysql
     
147. ln -fs /usr/local/mysql/bin/mysql /usr/bin/
     
148. echo "-----------------------------恭喜！MYSQL 从库安装成功--------------------------------------"
     
149. end_time=$(date +%s)
     
150. execution_time=$((end_time - start_time))
     
151. echo "脚本执行时间：${execution_time} 秒"
     
152. MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.err`
     
153. mysqladmin  -uroot -p${MYSQL_OLDPASSWORD} password ${MYSQL_ROOT_PASSWORD}
     
154. mysql -uroot -p123456 -e "update mysql.user set host ='%' where user ='root';"
     
155. mysql -uroot -p123456 -e "flush privileges;"
     
156. mysql -uroot -p123456 << EOF
     
157. exit
     
158. EOF
     
159. sleep 10
     

特别说明：此脚本为1.0版本，还有很多问题，希望大家多提意见，我们继续优化迭代。另外，要感谢国内知名MySQL数据库架构师李伟的鼎力支持，后续我们还会继续合作分享MySQL相关干货。