MySQL多实例
多实例介绍
mysql多实例:
1.有多个配置文件
2.多个端口、多个进程
3.多个socket文件
4.多个日志文件
5.多个server_id
多实例配置
#!/bin/bash
[ -f /etc/init.d/functions ] && source /etc/init.d/functions || echo "函数库不存在!"
mysql_dir=/usr/local/mysql/
shili_dir=/service/
sl1=3307
sl2=3308
sl3=3309
mima1=123
mima2=123
mima3=123
state() {
if [ $? -eq 0 ];then
action "$ok 成功" /bin/true
echo "$(date "+%F %T") $ok 成功" >>/tmp/mysql_shili_acc.log
else
action "$ok 失败" /bin/false
echo "$(date "+%F %T") $ok 失败" >>/tmp/mysql_shili_err.log
exit
fi
}
ok="创建数据库多实例存放目录"
mkdir -p $shili_dir{$sl1,$sl2,$sl3}
state
ok="配置数据库实例$sl1的配置文件"
cat > ${shili_dir}$sl1/my.cnf << EOF
[mysqld]
basedir=$mysql_dir
datadir=${shili_dir}$sl1/data
port=$sl1
socket=${shili_dir}$sl1/mysql.sock
log-error=${shili_dir}$sl1/data/mysql.err
log-bin=${shili_dir}$sl1/data/mysql-bin
server_id=7
EOF
state
ok="配置数据库实例$sl2的配置文件"
cat > ${shili_dir}$sl2/my.cnf << EOF
[mysqld]
basedir=$mysql_dir
datadir=${shili_dir}$sl2/data
port=$sl2
socket=${shili_dir}$sl2/mysql.sock
log-error=${shili_dir}$sl2/data/mysql.err
log-bin=${shili_dir}$sl2/data/mysql-bin
server_id=8
EOF
state
ok="配置数据库实例$sl3的配置文件"
cat > ${shili_dir}$sl3/my.cnf << EOF
[mysqld]
basedir=$mysql_dir
datadir=${shili_dir}$sl3/data
port=$sl3
socket=${shili_dir}$sl3/mysql.sock
log-error=${shili_dir}$sl3/data/mysql.err
log-bin=${shili_dir}$sl3/data/mysql-bin
server_id=9
EOF
state
ok="初始化数据库实例$sl1"
${mysql_dir}scripts/mysql_install_db --defaults-file=${shili_dir}$sl1/my.cnf --user=mysql --basedir=${mysql_dir} --datadir=${shili_dir}$sl1/data &> /dev/null
state
ok="初始化数据库实例$sl2"
${mysql_dir}scripts/mysql_install_db --defaults-file=${shili_dir}$sl2/my.cnf --user=mysql --basedir=${mysql_dir} --datadir=${shili_dir}$sl2/data &> /dev/null
state
ok="初始化数据库实例$sl3"
${mysql_dir}scripts/mysql_install_db --defaults-file=${shili_dir}$sl3/my.cnf --user=mysql --basedir=${mysql_dir} --datadir=${shili_dir}$sl3/data &> /dev/null
state
ok="授权数据库实例目录"
chown -R mysql.mysql $shili_dir
state
ok="启动数据库实例$sl1"
${mysql_dir}bin/mysqld_safe --defaults-file=${shili_dir}$sl1/my.cnf & &> /dev/null
sleep 5
netstat -lntp | grep $sl1
state
ok="启动数据库实例$sl2"
${mysql_dir}bin/mysqld_safe --defaults-file=${shili_dir}$sl2/my.cnf & &> /dev/null
sleep 5
netstat -lntp | grep $sl2
state
ok="启动数据库实例$sl3"
${mysql_dir}bin/mysqld_safe --defaults-file=${shili_dir}$sl3/my.cnf & &> /dev/null
sleep 5
netstat -lntp | grep $sl3
state
sleep 3
ok="设置数据库实例$sl1登陆密码"
${mysql_dir}bin/mysqladmin -uroot password "$mima1" -S ${shili_dir}$sl1/mysql.sock
state
ok="设置数据库实例$sl2登陆密码"
${mysql_dir}bin/mysqladmin -uroot password "$mima2" -S ${shili_dir}$sl2/mysql.sock
state
ok="设置数据库实例$sl3登陆密码"
${mysql_dir}bin/mysqladmin -uroot password "$mima3" -S ${shili_dir}$sl3/mysql.sock
state
ok="设置数据库实例$sl1登陆命令"
cat > /usr/bin/mysql$sl1 << EOF
${mysql_dir}bin/mysql -uroot -p$mima1 -S ${shili_dir}$sl1/mysql.sock
EOF
state
ok="设置数据库实例$sl2登陆命令"
cat > /usr/bin/mysql$sl2 << EOF
${mysql_dir}bin/mysql -uroot -p$mima2 -S ${shili_dir}$sl2/mysql.sock
EOF
state
ok="设置数据库实例$sl3登陆命令"
cat > /usr/bin/mysql$sl3 << EOF
${mysql_dir}bin/mysql -uroot -p$mima3 -S ${shili_dir}$sl3/mysql.sock
EOF
state
chmod +x /usr/bin/mysql330*
多实例主从
1、主库配置(3307数据库)
mysql> grant replication slave on *.* to rep@'127.0.0.1' identified by '123';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 662 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2、从库配置(3308数据库)
主库是谁?
做主从同步的用户是谁?
做主从同步的密码是多少?
端口?
从哪里同步?binlog名字?binlog的位置点?
mysql> change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=662;
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rep
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 662
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主从故障解决
----------------------------------------------------------
1.检测网络,因为是多实例主从在一台主机上【如果是多主机的主从就 ping 主库的IP】
[root@db01 ~]
2.检测端口
[root@db01 ~]
3.检测账号密码
[root@db01 ~]
4.从库根据主库查看的binlog信息,重新配置主库信息
change master to
master_host='127.0.0.1',
master_user='rep',
master_password='123',
master_port=3307,
master_log_file='mysql-bin.000003',
master_log_pos=753;
----------------------------------------------------------
----------------------------------------------------------
先将主库数据导出,再将导出的数据导入从库,再重新做主从
----------------------------------------------------------