mysql一机多实例数据库和主从同步
1. 创建配置文件。
恐怕很少有人会这么变态， 一台机器上要部署2个mysql实例，且还要配置上主从同步。 好吧，不过这样也是可以的。用来进行学习也不错。
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = root
password = rott
[mysqld1]
socket = /tmp/mysql.sock1
port = 3307
pid-file = /usr/local/mysql/var1/hostname.pid1
datadir = /usr/local/mysql/var1
user = root
server-id=1
log-error=/var/log/mysqld1.log
binlog-do-db = newdatabase
log_bin=mysql-bin
[mysqld2]
socket = /tmp/mysql.sock2
port = 3308
pid-file = /usr/local/mysql/var2/hostname.pid2
datadir = /usr/local/mysql/var2
user = root
server-id = 2
replicate-do-db = newdatabase
log-slave-updates
log-error=/var/log/mysqld2.log
2. 启动实例
mysqld_multi start 1
mysqld_multi start 2
3. 主数据库配置
mysql -S/tmp/mysql.sock1 -uroot （登陆第一个实例, 主mysql数据库） 如果有密码，请指定 -p
GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;
USE newdatabase;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 107 | newdatabase | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
mysqldump -u root -p –opt newdatabase > newdatabase.sql
UNLOCK TABLES;
exit;
4. 从库配置
stop slave;
CREATE DATABASE newdatabase;
mysql -u root -p newdatabase < /path/to/newdatabase.sql
CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;
START SLAVE;
SHOW SLAVE STATUS\G;
此篇文章已被阅读2582 次