mysql主从同步配置
主: 192.168.1.242
从：192.168.1.241
一、设置主库
1、修改主库my.cnf,主要是设置个不一样的id和logbin
[root@viyin.net testsvn]# vi /etc/my.cnf
server-id = 1
log-bin=mysql-bin
2 添加一个复制权限的授权一个帐号
mysql> grant replication slave on *.* to viyin@’192.168.1.%’ identified by ‘viyin.net’;
3 检查创建是否成功
mysql> select user,host from mysql.user;
+——-+————-+
| user | host |
+——-+————-+
| vi | % |
| root | 127.0.0.1 |
| viyin | 192.168.1.% |
| | localhost |
| root | localhost |
| | viyin.net |
+——-+————-+
4 锁主库表
mysql> flush tables with read lock;
5 显示主库信息
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000006 | 1687279 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
6 打包数据库
[root@viyin.net mysql]# tar zcvf /root/var.tar.gz /usr/local/mysql/var
tar: Removing leading `/’ from member names
7 迁移数据
[root@viyin.net mysql]# scp -P 2222 /root/var.tar.gz 192.168.1.241:/root
root@192.168.1.241′s password:
var.tar.gz 100% 1167KB 1.1MB/s 00:00
二、设置从服务器
1 解包（不是生产环境，俺把mysql/var目录全删了）
2 解锁
mysql> unlock tables;
3 编辑配置文件
#vim /etc/my.cnf
server-id = 2
master-host = 192.168.1.242
master-user = viyin
master-password = viyin.net
master-port = 3306
4 重启mysql
5 在从库上设置同步
设置连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position
mysql> slave stop;
mysql> change master to master_host=’192.168.1.242′,master_user=’vi’, master_password=’vivi’,
master_log_file=’mysql-bin.000006′, master_log_pos=1687279;
6、启动从库服务
mysql> slave start;
7 测试
mysql> CREATE TABLE `vi` (
-> `id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
-> `username` VARCHAR( 20 ) NOT NULL ,
-> `password` CHAR( 32 ) NOT NULL ,
-> `time` DATETIME NOT NULL ,
-> `number` FLOAT( 10 ) NOT NULL ,
-> `content` TEXT NOT NULL ,
-> PRIMARY KEY ( `id` )
-> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.242
Master_User: vi
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1687606
Relay_Log_File: vi13-relay-bin.000002
Relay_Log_Pos: 578
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1687606
Relay_Log_Space: 732
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
增加：
#需要备份的库
binlog-do-db=viyin
#忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=test
主从上都要设置
