关于mysql的主从复制和文件位置

首先是文件位置,以前放到了docker里,结果docker容器崩溃了,数据也就找不回来了,
想到放共享文件夹,球猫说专业名词叫volume,好吧,其实就是个v的参数,下边开始从ubuntu开始,

mkdir mydata
docker run -ti -h mysql_1 --name mysql_1 -v /home/user/mydata:/mysqldata -p 10001:3306 ubuntu
apt-get update
apt-get install mysql-server
apt-get install vim
vi /etc/mysql/mysql.conf.d/mysqld.cnf
#修改datadir为/mysqldata,注释掉bind-address=127.0.0.1
cp /var/lib/mysql/. /mysqldata -r -a
service mysql restart
mysql -u root -p

CREATE USER 'test' IDENTIFIED BY '****';
create database testDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
grant all privileges on testDB.* to test@"%" identified by '****' with grant option;
flush privileges;
exit

应该就搞定了,可以通过客户端访问了,结果发现mydata文件夹是空的,后来通过find找到在这里,看来要写绝对路径/var/lib/docker/volumes/mydata/_data/

下面就是主从复制了,再建一个mysql容器

mkdir mydata2
docker run -ti -h mysql_2 --name mysql_2 -v /home/user/mydata2:/mysqldata -p 10002:3306 ubuntu
...

似乎很多重复过程,可以中间exit后保存一份镜像再继续run
docker commit -m "mysql ok" mysql_1 ubuntu:mysql

如果主库有了数据,就需要先备份一下

mysqldump -uroot -p'****' -S /run/mysqld/mysqld.sock --all-databases > /mysqldata/mysql_bak.$(date +%F).sql
cp /var/lib/docker/volumes/mydata/_data/mysql_bak.2018-03-2x.sql mydata2
mysql -uroot -p'****' -S /run/mysqld/mysqld.sock < /mysqldata/mysql_bak.2018-03-2x.sql

获取所有容器的ip

docker inspect -f '{{.Name}} - {{.NetworkSettings.IPAddress }}' $(docker ps -aq)

主机

vi /etc/mysql/mysql.conf.d/mysqld.cnf
#去掉Server-id和log-bin的注释
service mysql restart
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'test'@'172.17.0.*' IDENTIFIED BY '****';
SHOW MASTER STATUS;
#记下日志的名字mysql-bin.000001和位置446

从机

vi /etc/mysql/mysql.conf.d/mysqld.cnf
#去掉Server-id的注释改为2
vi /mysqldata/auto.cnf
#修改uuid
service mysql restart
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='172.17.0.x', MASTER_USER='test', MASTER_PASSWORD='****', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=446;
start slave;
SHOW SLAVE STATUS\G

这样就好了,一下是一些笔记

show binlog events;
show binlog events in 'mysql-bin.000001';
show variables like '%server_uuid%';
stop slave;set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;
find / -name mysqld.sock
cat /var/log/mysql/error.log
#!/bin/sh
service mysql start
/bin/bash

参考资料,排名不分先后
http://www.cnblogs.com/phpstudy2015-6/p/6485819.html
https://blog.csdn.net/seteor/article/details/17264633
https://www.cnblogs.com/phpstudy2015-6/p/6706465.html

标签: none

添加新评论