mysql 主存库

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，才表明状态正常。