sudo vim /etc/mysql/my. cnf
1.配置主库
首先主库要开启远程连接，
server-id
= 1
log_bin
= /var/log/mysql/mysql-bin.log
expire_logs_days
= 10
max_binlog_size
= 100M
binlog_do_db = dbromwe
innodb_file_per_table = 1 #防止innodb日志文件过大
保存配置，并重启
重启后登陆mysql
查看是否配置成功，
执行
mysql>show master status\G;
+------------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 430 |
| |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
配置成功
GRANT REPLICATION SLAVE ON *.* TO backup@172.16.0.181 IDENTIFIED BY ‘backuppassword’;
格式：mysql>
GRANT REPLICATION SLAVE ON *.*
-> TO ‘帐号’@'从服务器IP或主机名’ IDENTIFIED BY ‘密码’;
(说明：ubuntu系统先要开启数据库远程连接配置)
2. 主库锁表
flush table with read lock;
3.导出数据库
执行
mysql>show master status\G;
+------------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 430 |
| |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记录file和position
mysqldump -udb -p 数据库> **.sql
4.解锁
unlock tables;
5.从库导入数据
mysql -udbromwe -p(密码) -h（host) dbromwe <××.sql
6.配置从库
server-id
= 10 #和主数据库不同，且和其他从数据库也不同
#log_bin
= /var/log/mysql/mysql-bin.log
expire_logs_days
= 10
max_binlog_size
= 100M
innodb_file_per_table = 1 #防止innodb日志文件过大
保存配置并重启
mysql命令行：
stop slave;
change master to master_host='ip',master_user='数据库名字',master_password='×××',master_log_file='mysql-bin.000003',master_log_pos=11702;
重启slave，执行
start slave;
主从同步检查
show slave status\G;
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。