Category Archives: MySQL

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

By Adamhuan | 2018年2月28日

脚本：

#!/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。

MySQL error：ERROR 1396 (HY000): Operation CREATE USER failed for ‘root’@’192.168.174.128’

By Adamhuan | 2018年2月28日

0 Comment

在MySQL中创建用户的时候可能会遇到如题所示的问题，具体如下：

mysql> create user 'root'@'192.168.174.128' identified by '*******';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'192.168.174.128'
mysql> 
mysql> INSERT INTO mysql.user (user, host, password)
    -> VALUES ('root', '192.168.174.127', PASSWORD('*******'));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
mysql>

mysql> create user 'root'@'192.168.174.128' identified by '*******';

ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'192.168.174.128'

mysql>

mysql> INSERT INTO mysql.user (user, host, password)

-> VALUES ('root', '192.168.174.127', PASSWORD('*******'));

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

mysql>

修改配置文件：

[root@mysql-2 ~]# service mysql stop
Shutting down MySQL.... SUCCESS! 
[root@mysql-2 ~]# 

[root@mysql-2 ~]# cat /etc/my.cnf | grep "sql-mode"
#sql-mode="NO_AUTO_CREATE_USER" #禁止创建用户不添加密码
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" #禁止创建用户不添加密码
[root@mysql-2 ~]# 
[root@mysql-2 ~]# service mysql start
Starting MySQL.. SUCCESS! 
[root@mysql-2 ~]#

[root@mysql-2 ~]# service mysql stop

Shutting down MySQL.... SUCCESS!

[root@mysql-2 ~]#

[root@mysql-2 ~]# cat /etc/my.cnf | grep "sql-mode"

#sql-mode="NO_AUTO_CREATE_USER" #禁止创建用户不添加密码

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" #禁止创建用户不添加密码

[root@mysql-2 ~]#

[root@mysql-2 ~]# service mysql start

Starting MySQL.. SUCCESS!

[root@mysql-2 ~]#

重新执行创建：
[crayon-5b06fad6aa1f0802[……]

MySQL主从 – error：[ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user ‘replme’@’192.168.174.129’ (using password: YES) (Errno: 1045), Error_code: 1597

By Adamhuan | 2018年2月28日

0 Comment

大概的错误如题所示，具体的报错日志如下所示：

2018-02-27 18:04:58 36668 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.174.128', master_port= 33944, master_log_file='mysql-1-bin.000005', master_log_pos= 1994, master_bind=''.
2018-02-27 18:04:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2018-02-27 18:04:58 36668 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2018-02-27 18:04:58 36668 [Note] Slave SQL thread initialized, starting replication in log 'mysql-1-bin.000005' at position 1994, relay log './mysql-2-relay-bin.000001' position: 4
2018-02-27 18:04:58 36668 [Note] Slave I/O thread: connected to master 'replme@192.168.174.128:33944',replication started in log 'mysql-1-bin.000005' at position 1994
2018-02-27 18:04:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597
2018-02-27 18:04:58 36668 [ERROR] Slave I/O thread couldn't register on master
2018-02-27 18:04:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597
2018-02-27 18:04:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2018-02-27 18:04:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597
2018-02-27 18:04:58 36668 [ERROR] Slave I/O thread couldn't register on master


2018-02-27 18:05:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597
2018-02-27 18:05:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2018-02-27 18:05:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597
2018-02-27 18:05:58 36668 [ERROR] Slave I/O thread couldn't register on master
2018-02-27 18:06:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597
2018-02-27 18:06:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2018-02-27 18:06:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597
2018-02-27 18:06:58 36668 [ERROR] Slave I/O thread couldn't register on master
2018-02-27 18:07:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597
2018-02-27 18:07:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2018-02-27 18:07:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597
2018-02-27 18:07:58 36668 [ERROR] Slave I/O thread couldn't register on master

2018-02-27 18:04:58 36668 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.174.128', master_port= 33944, master_log_file='mysql-1-bin.000005', master_log_pos= 1994, master_bind=''.

2018-02-27 18:04:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2018-02-27 18:04:58 36668 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

2018-02-27 18:04:58 36668 [Note] Slave SQL thread initialized, starting replication in log 'mysql-1-bin.000005' at position 1994, relay log './mysql-2-relay-bin.000001' position: 4

2018-02-27 18:04:58 36668 [Note] Slave I/O thread: connected to master 'replme@192.168.174.128:33944',replication started in log 'mysql-1-bin.000005' at position 1994

2018-02-27 18:04:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597

2018-02-27 18:04:58 36668 [ERROR] Slave I/O thread couldn't register on master

2018-02-27 18:04:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597

2018-02-27 18:04:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597

2018-02-27 18:04:58 36668 [ERROR] Slave I/O thread couldn't register on master

2018-02-27 18:05:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597

2018-02-27 18:05:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2018-02-27 18:05:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597

2018-02-27 18:05:58 36668 [ERROR] Slave I/O thread couldn't register on master

2018-02-27 18:06:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597

2018-02-27 18:06:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2018-02-27 18:06:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597

2018-02-27 18:06:58 36668 [ERROR] Slave I/O thread couldn't register on master

2018-02-27 18:07:58 36668 [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log 'mysql-1-bin.000005' at position 1994, Error_code: 1597

2018-02-27 18:07:58 36668 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2018-02-27 18:07:58 36668 [ERROR] Slave I/O: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045), Error_code: 1597

2018-02-27 18:07:58 36668 [ERROR] Slave I/O thread couldn't register on master

数据库查看主从状态的时候，你会看到这样的现象：
从库

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting to reconnect after a failed registration on master
                  Master_Host: 192.168.174.128
                  Master_User: replme
                  Master_Port: 33944
                Connect_Retry: 60
              Master_Log_File: mysql-1-bin.000005
          Read_Master_Log_Pos: 1994
               Relay_Log_File: mysql-2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-1-bin.000005
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1994
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1597
                Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 24
                  Master_UUID: 4132b4a2-1b83-11e8-90ee-000c29ca975b
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 180227 18:04:58
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

mysql>

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting to reconnect after a failed registration on master

Master_Host: 192.168.174.128

Master_User: replme

Master_Port: 33944

Connect_Retry: 60

Master_Log_File: mysql-1-bin.000005

Read_Master_Log_Pos: 1994

Relay_Log_File: mysql-2-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-1-bin.000005

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 1994

Relay_Log_Space: 120

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1597

Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'replme'@'192.168.174.129' (using password: YES) (Errno: 1045)

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 24

Master_UUID: 4132b4a2-1b83-11e8-90ee-000c29ca975b

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 180227 18:04:58

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

mysql>

问题的解决：
主库端，用于主从复制的用户缺少[……]

MySQL – Error：libnuma.so.1()(64bit) is needed by MySQL-server-5.6.39-1.el6.x86_64

By Adamhuan | 2018年2月1日

0 Comment

用脚本安装MySQL的时候遇到如题所示的错误，具体如下：

warning: /opt/software/rpm_data/MySQL-server-5.6.39-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
        libnuma.so.1()(64bit) is needed by MySQL-server-5.6.39-1.el6.x86_64
        libnuma.so.1(libnuma_1.1)(64bit) is needed by MySQL-server-5.6.39-1.el6.x86_64
        libnuma.so.1(libnuma_1.2)(64bit) is needed by MySQL-server-5.6.39-1.el6.x86_64

warning: /opt/software/rpm_data/MySQL-server-5.6.39-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: Failed dependencies:

libnuma.so.1()(64bit) is needed by MySQL-server-5.6.39-1.el6.x86_64

libnuma.so.1(libnuma_1.1)(64bit) is needed by MySQL-server-5.6.39-1.el6.x86_64

libnuma.so.1(libnuma_1.2)(64bit) is needed by MySQL-server-5.6.39-1.el6.x86_64

缺少软件包：
http://mirror.centos.org/centos/6/os/x86_64/Packages/numactl-2.0.9-2.el6.x8[……]

MySQL：查询包含某个列名的表

By Adamhuan | 2018年1月15日

0 Comment

没有太多说的，直接看代码：

MariaDB [(none)]> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.columns where column_name='name';
+--------------------+-------------------------------+-------------+
| TABLE_SCHEMA       | TABLE_NAME                    | COLUMN_NAME |
+--------------------+-------------------------------+-------------+
| information_schema | INNODB_SYS_TABLESTATS         | NAME        |
| information_schema | INNODB_TABLESPACES_SCRUBBING  | NAME        |
| information_schema | INNODB_TABLESPACES_ENCRYPTION | NAME        |
| information_schema | INNODB_SYS_FIELDS             | NAME        |
| information_schema | INNODB_SYS_COLUMNS            | NAME        |
| information_schema | INNODB_SYS_TABLES             | NAME        |
| information_schema | INNODB_SYS_TABLESPACES        | NAME        |
| information_schema | INNODB_METRICS                | NAME        |
| information_schema | INNODB_SYS_INDEXES            | NAME        |
| information_schema | INNODB_MUTEXES                | NAME        |
| mysql              | event                         | name        |
| mysql              | func                          | name        |
| mysql              | help_category                 | name        |
| mysql              | help_keyword                  | name        |
| mysql              | help_topic                    | name        |
| mysql              | me                            | name        |
| mysql              | plugin                        | name        |
| mysql              | proc                          | name        |
| mysql              | time_zone_name                | Name        |
| performance_schema | cond_instances                | NAME        |
| performance_schema | mutex_instances               | NAME        |
| performance_schema | rwlock_instances              | NAME        |
| performance_schema | setup_consumers               | NAME        |
| performance_schema | setup_instruments             | NAME        |
| performance_schema | setup_timers                  | NAME        |
| performance_schema | threads                       | NAME        |
+--------------------+-------------------------------+-------------+
26 rows in set (0.02 sec)

MariaDB [(none)]>

MariaDB [(none)]> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.columns where column_name='name';

+--------------------+-------------------------------+-------------+

| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |

+--------------------+-------------------------------+-------------+

| information_schema | INNODB_SYS_TABLESTATS | NAME |

| information_schema | INNODB_TABLESPACES_SCRUBBING | NAME |

| information_schema | INNODB_TABLESPACES_ENCRYPTION | NAME |

| information_schema | INNODB_SYS_FIELDS | NAME |

| information_schema | INNODB_SYS_COLUMNS | NAME |

| information_schema | INNODB_SYS_TABLES | NAME |

| information_schema | INNODB_SYS_TABLESPACES | NAME |

| information_schema | INNODB_METRICS | NAME |

| information_schema | INNODB_SYS_INDEXES | NAME |

| information_schema | INNODB_MUTEXES | NAME |

| mysql | event | name |

| mysql | func | name |

| mysql | help_category | name |

| mysql | help_keyword | name |

| mysql | help_topic | name |

| mysql | me | name |

| mysql | plugin | name |

| mysql | proc | name |

| mysql | time_zone_name | Name |

| performance_schema | cond_instances | NAME |

| performance_schema | mutex_instances | NAME |

| performance_schema | rwlock_instances | NAME |

| performance_schema | setup_consumers | NAME |

| performance_schema | setup_instruments | NAME |

| performance_schema | setup_timers | NAME |

| performance_schema | threads | NAME |

+--------------------+-------------------------------+-------------+

26 rows in set (0.02 sec)

MariaDB [(none)]>

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