mysql一机多实例数据库和主从同步

Life is short , "play" more!
本文来自lihao's Blog,转载请注明。

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;

此篇文章已被阅读1840 次


发表评论

电子邮件地址不会被公开。 必填项已用*标注