Category Archives: MySQL

密码保护：Shell：MySQL – 备份【Binlog】

By Adamhuan | 2018年7月5日

无法提供摘要。这是一篇受保护的文章。

MySQL 5.7.22：mysqldump: Couldn’t execute ‘SELECT /!40001 SQL_NO_CACHE / * FROM `GLOBAL_STATUS`’: The ‘INFORMATION_SCHEMA.GLOBAL_STATUS’ feature is disabled

By Adamhuan | 2018年7月5日

今天在用脚本做备份的时候，遇到了如题所示的报错。
具体如下：

[root@mysql0 shell]# sh mysql_backup.sh 
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Schema List with Space : [mysql performance_schema information_schema sys ]
*******
file is: [/data/backup/mysql/MySQL_db_backup_LIST_mysql_metadata.20180705_14_58_44]
log is: [/data/backup/mysql/MySQL_db_backup_LIST_mysql_metadata.20180705_14_58_44.log]
0000000000000
MySQL backup - schema [mysql performance_schema information_schema sys ]
Date time - Begin: [20180705_14_58_44]
0000000000000

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `GLOBAL_STATUS`': The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' (3167)
Date time - Finished: [20180705_14_58_44]
[root@mysql0 shell]#

[root@mysql0 shell]# sh mysql_backup.sh

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Schema List with Space : [mysql performance_schema information_schema sys ]

*******

file is: [/data/backup/mysql/MySQL_db_backup_LIST_mysql_metadata.20180705_14_58_44]

log is: [/data/backup/mysql/MySQL_db_backup_LIST_mysql_metadata.20180705_14_58_44.log]

0000000000000

MySQL backup - schema [mysql performance_schema information_schema sys ]

Date time - Begin: [20180705_14_58_44]

0000000000000

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `GLOBAL_STATUS`': The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' (3167)

Date time - Finished: [20180705_14_58_44]

[root@mysql0 shell]#

上面的问题是因为一个参数导致的【show_compatibility_56】
从mysql5.7.6开始information_schema.global_s[……]

Percona MySQL 5.7.22：修改默认的数据路径的概览

By Adamhuan | 2018年7月5日

0 Comment

需要重新定义配置文件：【my.cnf】

[root@mysql0 mysql]# cat /etc/my.cnf
# Percona Server template configuration

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

datadir=/data/mysql/data
socket=/data/mysql/mysql.sock

log-bin=/data/mysql/log-bin/mysql-bin
server-id=1

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

user=mysql

[client]
socket=/data/mysql/mysql.sock
[root@mysql0 mysql]#

[root@mysql0 mysql]# cat /etc/my.cnf

# Percona Server template configuration

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

datadir=/data/mysql/data

socket=/data/mysql/mysql.sock

log-bin=/data/mysql/log-bin/mysql-bin

server-id=1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

user=mysql

[client]

socket=/data/mysql/mysql.sock

[root@mysql0 mysql]#

然后，启动服务：
service mysql start

然后进入数据库后，对密码做出修改：
alter user root@’localhost’ identified b[……]

SHELL + MySQL：MySQL – 备份脚本

By Adamhuan | 2018年6月28日

0 Comment

直接看代码：

#!/bin/bash

# File name : mysql_backup.sh

# variable
  # Time
str_date=$(date "+%Y%m%d_%H_%M_%S")
  # File and Path
path_data_backup=/cloudera/backup
  # MySQL
mysql_user=root
mysql_passwd="*****"

# function
function do_mysql_backup_single(){
  # intro
  echo "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%"

  # variable
  f_schema="$1"

  f_file_file=$path_data_backup"/MySQL_db_backup_SINGLE_"$f_schema"."$str_date
  f_file_log=$f_file_file".log"

  # display
  echo "*******"
  echo "file is: [$f_file_file]"
  echo "log is: [$f_file_log]"

  echo "0000000000000" | tee -a $f_file_log
  echo "MySQL backup - schema [$f_schema]" | tee -a $f_file_log
  echo "Date time - Begin: [$str_date]" | tee -a $f_file_log
  echo "0000000000000" | tee -a $f_file_log
  echo "" | tee -a $f_file_log

  # do backup
  mysqldump -u$mysql_user -p$mysql_passwd --lock-tables --events --databases $f_schema > $f_file_file | tee -a $f_file_log

  # end
  echo "Date time - Finished: [$str_date]" | tee -a $f_file_log

}

function do_mysql_backup_list(){

  # intro
  echo "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%"

  # variable
  f_list_schema="$1"
  f_schema_array=(${f_list_schema//,/ })
  f_schema_string_with_space=""

  f_file_name="$2"

  # do
  for schema_list_item in ${f_schema_array[@]}
  do
    #echo "============="
    #echo "Current is: [$schema_list_item]"

    # do
    #do_mysql_backup_single $schema_list_item
    f_schema_string_with_space+=$schema_list_item" "

  done
  echo "Schema List with Space : [$f_schema_string_with_space]"

  f_file_file=$path_data_backup"/MySQL_db_backup_LIST_"$f_file_name"."$str_date
  f_file_log=$f_file_file".log"

  # display
  echo "*******"
  echo "file is: [$f_file_file]"
  echo "log is: [$f_file_log]"

  echo "0000000000000" | tee -a $f_file_log
  echo "MySQL backup - schema [$f_schema_string_with_space]" | tee -a $f_file_log
  echo "Date time - Begin: [$str_date]" | tee -a $f_file_log
  echo "0000000000000" | tee -a $f_file_log
  echo "" | tee -a $f_file_log

  # do backup
  #mysqldump -u$mysql_user -p$mysql_passwd --lock-tables --databases $f_schema_string_with_space > $f_file_file | tee -a $f_file_log
  mysqldump -u$mysql_user -p$mysql_passwd --lock-tables --skip-lock-tables --databases $f_schema_string_with_space > $f_file_file | tee -a $f_file_log

  # end
  echo "Date time - Finished: [$str_date]" | tee -a $f_file_log

}

# begin

  # do backup
do_mysql_backup_list "people god devil" "business_data"
do_mysql_backup_list "mysql performance_schema sys" "mysql_metadata"

# end


# finished

100

101

#!/bin/bash

# File name : mysql_backup.sh

# variable

# Time

str_date=$(date "+%Y%m%d_%H_%M_%S")

# File and Path

path_data_backup=/cloudera/backup

# MySQL

mysql_user=root

mysql_passwd="*****"

# function

function do_mysql_backup_single(){

# intro

echo "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%"

# variable

f_schema="$1"

f_file_file=$path_data_backup"/MySQL_db_backup_SINGLE_"$f_schema"."$str_date

f_file_log=$f_file_file".log"

# display

echo "*******"

echo "file is: [$f_file_file]"

echo "log is: [$f_file_log]"

echo "0000000000000" | tee -a $f_file_log

echo "MySQL backup - schema [$f_schema]" | tee -a $f_file_log

echo "Date time - Begin: [$str_date]" | tee -a $f_file_log

echo "0000000000000" | tee -a $f_file_log

echo "" | tee -a $f_file_log

# do backup

mysqldump -u$mysql_user -p$mysql_passwd --lock-tables --events --databases $f_schema > $f_file_file | tee -a $f_file_log

# end

echo "Date time - Finished: [$str_date]" | tee -a $f_file_log

}

function do_mysql_backup_list(){

# intro

echo "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%"

# variable

f_list_schema="$1"

f_schema_array=(${f_list_schema//,/ })

f_schema_string_with_space=""

f_file_name="$2"

# do

for schema_list_item in ${f_schema_array[@]}

#echo "============="

#echo "Current is: [$schema_list_item]"

# do

#do_mysql_backup_single $schema_list_item

f_schema_string_with_space+=$schema_list_item" "

done

echo "Schema List with Space : [$f_schema_string_with_space]"

f_file_file=$path_data_backup"/MySQL_db_backup_LIST_"$f_file_name"."$str_date

f_file_log=$f_file_file".log"

# display

echo "*******"

echo "file is: [$f_file_file]"

echo "log is: [$f_file_log]"

echo "0000000000000" | tee -a $f_file_log

echo "MySQL backup - schema [$f_schema_string_with_space]" | tee -a $f_file_log

echo "Date time - Begin: [$str_date]" | tee -a $f_file_log

echo "0000000000000" | tee -a $f_file_log

echo "" | tee -a $f_file_log

# do backup

#mysqldump -u$mysql_user -p$mysql_passwd --lock-tables --databases $f_schema_string_with_space > $f_file_file | tee -a $f_file_log

mysqldump -u$mysql_user -p$mysql_passwd --lock-tables --skip-lock-tables --databases $f_schema_string_with_space > $f_file_file | tee -a $f_file_log

# end

echo "Date time - Finished: [$str_date]" | tee -a $f_file_log

}

# begin

# do backup

do_mysql_backup_list "people god devil" "business_data"

do_mysql_backup_list "mysql performance_schema sys" "mysql_metadata"

# end

# finished

—————————————————
Done。

SHELL：MySQL – 自动创建主从同步

By Adamhuan | 2018年2月28日

0 Comment

脚本：

#!/bin/bash

# file: mysql_ha_master_slave_auto_config.sh
# purpose: 自动配置MySQL主从架构

# 前提：
# 1. 已经安装好了MySQL的两台服务器
# 2. 服务器服务已运行
# 3. 服务器之间的SSH互信

# 默认情况下，该脚本在主库端执行


# =====================
# variable

# ----------
# 服务器相关
ip_master=192.168.174.128
ip_slave=192.168.174.129

# 当前服务器角色：
# 1，主库端
# 2，从库端
current_role=""

os_user='root'
os_password='*************'

# ----------
# 数据库相关

# 用户
mysql_root_user='root'
mysql_root_password='*******'

mysql_repl_user='replme'
mysql_repl_password='*******'

# 配置
mysql_port='33944'

# 文件
file_mysql_cnf='/etc/my.cnf'

# 参数

# server-id
#mysql_parameter_server_id_master=0
#mysql_parameter_server_id_slave=0
#mysql_parameter_server_id_diff=5

# 主从相关信息
mysql_replication_file=""
mysql_replication_pos=0

# ----------

# ----------

# ----------

# =====================
# function

# ------------
function do_sql() {
  # variable
  func_str_ip="$1"
  func_str_sql="$2"

  # ----------------------
  # changed

  func_str_user="$3"
  func_str_password="$4"
  func_str_port="$5"

  # ----------------------

  #echo "func_str_ip --> $func_str_ip"
  #echo "func_str_sql --> $func_str_sql"
  #echo "func_str_user --> $func_str_user"
  #echo "func_str_password --> $func_str_password"
  #echo "func_str_port --> $func_str_port"

  echo

  # action
  # 本场景中不涉及到对MySQL某个库的操作，所以没有选择[db]
  # mysql -u $user -p"$password" $db -N -e "$f_sql_str"
  mysql -h"$func_str_ip" -P$func_str_port -u "$func_str_user" -p"$func_str_password" -N -e "$func_str_sql"
}

# ------------
# 对指定主机执行Linux命令
# 前提：
# 1. IP可达
# 2. SSH等价关系
function do_linux_by_ssh() {
  # variable
  func_str_ip="$1"
  func_str_user="$2"
  func_str_command="$3"

  # action
  ssh -t $func_str_user@$func_str_ip "$func_str_command"
}

# ------------
# 检查当前运行服务器角色
function check_machine_role() {
  f_current_ip=$(ifconfig | grep "inet addr" | head -n 1 | cut -d':' -f2 | cut -d' ' -f1)

  if [ "$f_current_ip" == "$ip_master" ]
  then
    current_role="1"
  elif [ "$f_current_ip" == "$ip_slave" ]
  then
    current_role="2"
  else
    echo "--> Not Match."
  fi

}

# ------------
# 计算和
function calc_sum() {
  #statements
  num1=$1
  num2=$2

  echo $(($num1+$num2))
}

# ------------
# 主库端操作
function mysql_master_config() {

  echo "--> 初步查看"
  do_sql "$ip_master" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"
  do_sql "$ip_master" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # 增加Replication用户
  echo "Add user replication"
  do_sql "$ip_master" "create user $mysql_repl_user@'$ip_slave' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"
  do_sql "$ip_master" "grant replication slave,replication client on *.* to $mysql_repl_user@'$ip_slave'" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # 增加root用户
  do_sql "$ip_master" "create user $mysql_root_user@'$ip_master' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"
  do_sql "$ip_master" "grant all privileges on *.* to $mysql_root_user@'$ip_master" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  do_sql "$ip_master" "create user $mysql_root_user@'$ip_slave' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"
  do_sql "$ip_master" "grant all privileges on *.* to $mysql_root_user@'$ip_slave" "$mysql_root_user" "$mysql_root_password" "$mysql_port"


  # 再次查看
  do_sql "$ip_master" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # 获取基本信息
  mysql_replication_file=`do_sql "$ip_master" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port" | awk '{printf $1}'`
  mysql_replication_pos=`do_sql "$ip_master" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port" | awk '{printf $2}'`

  # display
  echo "主库端：file - [$mysql_replication_file]; POS - [$mysql_replication_pos]"

}

# ------------
# 从库端操作
function mysql_slave_config() {
  # 初步查看
  do_sql "$ip_slave" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"
  do_sql "$ip_slave" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # 增加用户 - root
  # 增加root用户
  echo "Add user"
  do_sql "$ip_slave" "create user $mysql_root_user@'$ip_master' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"
  do_sql "$ip_slave" "grant all privileges on *.* to $mysql_root_user@'$ip_master" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  do_sql "$ip_slave" "create user $mysql_root_user@'$ip_slave' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"
  do_sql "$ip_slave" "grant all privileges on *.* to $mysql_root_user@'$ip_slave" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # 再次查看

  do_sql "$ip_slave" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # Change master
  echo "Change Master"
  echo "--------------"
  echo "Command is:"
  "change master to master_host='$ip_master',master_port=$mysql_port,master_user='$mysql_repl_user',master_password='$mysql_repl_password',master_log_file='$mysql_replication_file',master_log_pos=$mysql_replication_pos;"
  echo "--------------"
  do_sql "$ip_slave" "change master to master_host='$ip_master',master_port=$mysql_port,master_user='$mysql_repl_user',master_password='$mysql_repl_password',master_log_file='$mysql_replication_file',master_log_pos=$mysql_replication_pos;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # display

  do_sql "$ip_slave" "show slave status\G" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

  # run
  echo "Start slave"
  do_sql "$ip_slave" "start slave" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

}

# =====================
# display

# =====================
# begin

# 判定当前服务器角色
check_machine_role
echo "当前脚本执行服务器角色：$current_role"

# 配置主库端
echo ""
echo "主库"
echo "==============="
mysql_master_config

# 配置从库端
echo ""
echo "从库"
echo "==============="
mysql_slave_config

# =====================
# end

# =====================
# Finished

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

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

#!/bin/bash

# file: mysql_ha_master_slave_auto_config.sh

# purpose: 自动配置MySQL主从架构

# 前提：

# 1. 已经安装好了MySQL的两台服务器

# 2. 服务器服务已运行

# 3. 服务器之间的SSH互信

# 默认情况下，该脚本在主库端执行

# =====================

# variable

# ----------

# 服务器相关

ip_master=192.168.174.128

ip_slave=192.168.174.129

# 当前服务器角色：

# 1，主库端

# 2，从库端

current_role=""

os_user='root'

os_password='*************'

# ----------

# 数据库相关

# 用户

mysql_root_user='root'

mysql_root_password='*******'

mysql_repl_user='replme'

mysql_repl_password='*******'

# 配置

mysql_port='33944'

# 文件

file_mysql_cnf='/etc/my.cnf'

# 参数

# server-id

#mysql_parameter_server_id_master=0

#mysql_parameter_server_id_slave=0

#mysql_parameter_server_id_diff=5

# 主从相关信息

mysql_replication_file=""

mysql_replication_pos=0

# ----------

# =====================

# function

# ------------

function do_sql() {

# variable

func_str_ip="$1"

func_str_sql="$2"

# ----------------------

# changed

func_str_user="$3"

func_str_password="$4"

func_str_port="$5"

# ----------------------

#echo "func_str_ip --> $func_str_ip"

#echo "func_str_sql --> $func_str_sql"

#echo "func_str_user --> $func_str_user"

#echo "func_str_password --> $func_str_password"

#echo "func_str_port --> $func_str_port"

echo

# action

# 本场景中不涉及到对MySQL某个库的操作，所以没有选择[db]

# mysql -u $user -p"$password" $db -N -e "$f_sql_str"

mysql -h"$func_str_ip" -P$func_str_port -u "$func_str_user" -p"$func_str_password" -N -e "$func_str_sql"

}

# ------------

# 对指定主机执行Linux命令

# 前提：

# 1. IP可达

# 2. SSH等价关系

function do_linux_by_ssh() {

# variable

func_str_ip="$1"

func_str_user="$2"

func_str_command="$3"

# action

ssh -t $func_str_user@$func_str_ip "$func_str_command"

}

# ------------

# 检查当前运行服务器角色

function check_machine_role() {

f_current_ip=$(ifconfig | grep "inet addr" | head -n 1 | cut -d':' -f2 | cut -d' ' -f1)

if [ "$f_current_ip" == "$ip_master" ]

then

current_role="1"

elif [ "$f_current_ip" == "$ip_slave" ]

then

current_role="2"

else

echo "--> Not Match."

}

# ------------

# 计算和

function calc_sum() {

#statements

num1=$1

num2=$2

echo $(($num1+$num2))

}

# ------------

# 主库端操作

function mysql_master_config() {

echo "--> 初步查看"

do_sql "$ip_master" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_master" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# 增加Replication用户

echo "Add user replication"

do_sql "$ip_master" "create user $mysql_repl_user@'$ip_slave' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_master" "grant replication slave,replication client on *.* to $mysql_repl_user@'$ip_slave'" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# 增加root用户

do_sql "$ip_master" "create user $mysql_root_user@'$ip_master' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_master" "grant all privileges on *.* to $mysql_root_user@'$ip_master" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_master" "create user $mysql_root_user@'$ip_slave' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_master" "grant all privileges on *.* to $mysql_root_user@'$ip_slave" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# 再次查看

do_sql "$ip_master" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# 获取基本信息

mysql_replication_file=`do_sql "$ip_master" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port" | awk '{printf $1}'`

mysql_replication_pos=`do_sql "$ip_master" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port" | awk '{printf $2}'`

# display

echo "主库端：file - [$mysql_replication_file]; POS - [$mysql_replication_pos]"

}

# ------------

# 从库端操作

function mysql_slave_config() {

# 初步查看

do_sql "$ip_slave" "show master status;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_slave" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# 增加用户 - root

# 增加root用户

echo "Add user"

do_sql "$ip_slave" "create user $mysql_root_user@'$ip_master' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_slave" "grant all privileges on *.* to $mysql_root_user@'$ip_master" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_slave" "create user $mysql_root_user@'$ip_slave' identified by '$mysql_repl_password';" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

do_sql "$ip_slave" "grant all privileges on *.* to $mysql_root_user@'$ip_slave" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# 再次查看

do_sql "$ip_slave" "select user,host from mysql.user;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# Change master

echo "Change Master"

echo "--------------"

echo "Command is:"

"change master to master_host='$ip_master',master_port=$mysql_port,master_user='$mysql_repl_user',master_password='$mysql_repl_password',master_log_file='$mysql_replication_file',master_log_pos=$mysql_replication_pos;"

echo "--------------"

do_sql "$ip_slave" "change master to master_host='$ip_master',master_port=$mysql_port,master_user='$mysql_repl_user',master_password='$mysql_repl_password',master_log_file='$mysql_replication_file',master_log_pos=$mysql_replication_pos;" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# display

do_sql "$ip_slave" "show slave status\G" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

# run

echo "Start slave"

do_sql "$ip_slave" "start slave" "$mysql_root_user" "$mysql_root_password" "$mysql_port"

}

# =====================

# display

# =====================

# begin

# 判定当前服务器角色

check_machine_role

echo "当前脚本执行服务器角色：$current_role"

# 配置主库端

echo ""

echo "主库"

echo "==============="

mysql_master_config

# 配置从库端

echo ""

echo "从库"

echo "==============="

mysql_slave_config

# =====================

# end

# =====================

# Finished

——————————————————
Done。