假设:
Mysql主数据库的ip:192.168.0.21 端口:3307
Mysql从数据库的ip:192.168.0.22 端口:3312
一、主服务器数据库操作
1.主数据库192.168.0.21停止写操作,如果这个数据库本身就是一个从数据库,那么stop slave;命令进行同步的停止。
2.主数据库192.168.0.21必须是开启binlog的,开启状态下执行show master status;命令,记录下当前的binlog位置。
mysql> show master status;
+------------------+----------+-----------------+--------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-----------------+--------------------+
| mysql-bin.084823 | 98377064 | | |
+------------------+----------+-----------------+--------------------+
1 row in set (0.00 sec)
3.在主数据库192.168.0.21中增加用于192.168.0.22进行数据同步的用户。
mysql>GRANT REPLICATION SLAVE ON *.* TO 'test_sync'@'192.168.0.22' IDENTIFIED BY 'your_pwd';
二、设置从数据库
1.如果从服务器的mysql是多实例的,则配置文件中需要增加mysqld节点,大致配置如下
[mysqld12]
port = 3312
server_id = 12 # id不要和主重复
pid-file = /var/run/mysqld/mysqld12.pid
socket = /var/run/mysqld/mysqld12.sock
Log_error=/var/log/mysql/error12.log
datadir = /var/lib/mysql12
log_bin = /var/log/mysql/binlog12/mysql-bin.log
relay_log = /var/log/mysql/relaylog12/mysqld-relay-bin.log
2.从服务器创建相应的文件目录并给予权限
mkdir /var/lib/mysql12
chown -R mysql:mysql /var/lib/mysql12
mkdir /var/log/mysql/binlog12
chown -R mysql:mysql /var/log/mysql/binlog12
mkdir /var/log/mysql/relaylog12
chown -R mysql:mysql /var/log/mysql/relaylog12
3.从数据库初始化
mysql_install_db --basedir=/usr --user=mysql --datadir=/var/lib/mysql12
4.启动从数据库实例
mysqld_multi start 12
5.查看启动日志看是否正常
vi /var/log/mysql/error12.log
6.如果没有问题现在就可以进入mysql了
mysql -S /var/run/mysqld/mysqld12.sock
7.先关闭mysql实例
mysqld_multi stop 12
从主服务器将数据库拷贝过来
以数据库名mydb为例,将mydb拷贝到/var/lib/mysql12/mydb下
chown -R mysql:mysql /var/lib/mysql12/mydb
另外还要拷贝ib_logfile0、ib_logfile1、ibdata1三个文件过来
拷贝以后也分别设置权限chown mysql:mysql ib*
8.再次启动从mysql实例
mysqld_multi start 12
9.设置从数据库的同步参数
mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.11',MASTER_USER='test_sync',Master_Port=3307,MASTER_PASSWORD='your_pwd',MASTER_LOG_FILE='mysql-bin.084823',MASTER_LOG_POS=98377064;
10.开始同步
mysql>start slave;
11.查看同步状态
mysql>show slave status\G;