环境:master:192.168.240.131
Slave:192.168.240.130
需要复制的数据库:test
1.Master服务器启动mysql,
a)#mysql–uroot–proot
b)创建一个有复制权限的用户,只限slave远程连接访问.
i.mysql>grantreplicationslaveon*.*tonavy@192.168.240.130 identifiedby‘password’;
ii.mysql>flushprivileges;
c)mysql>flushtableswithreadlock;#锁定master服务器所有表的写入。
d)重新打开一终端,备份要复制的数据库。
i.#tar-zcvftest.tar.gz/data/mysql_db/test///test所在路径/data/mysql_db/test/即一个库。
ii.将test.tar.gz复制到从服务器上并减压到相应的目录下
e)返回上一终端。
i.Mysql>showmasterstatus;
+------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
|mysql-bin.000013|106|test||
+------------------+----------+--------------+------------------+
1rowinset(0.01sec)
其中mysql-bin.000013和106二个值将是slave与master的同步点。
f)给数据库解锁(当备份完成后)mysql>unlocktables;
g)编辑mysql的配置文件。Vim/etc/f设置这三个参数,没有的添加,有的直接更改即可。
log-bin=mysql-bin
server-id=1
binlog-do-db=test
保存退出。
2.Slave服务器配置
a)将从master中备份的库解压到相应路径下(退库的导入)
i.#tarzxvftest.tar.gz
b)修改f
server-id=2
master-host=192.168.240.131
master-user=navy
master-password=password
log-bin=
3.重启master,slave的mysql服务
a)注意顺序,先重启master-à然后是slave.
b)Slave服务器重启后,登录mysql
mysql>stopslave;
QueryOK,0rowsaffected(0.00sec)
mysql>changemasterto
->master_host='192.168.0.3',
->master_user='navy',
->master_password='password',
->master_log_file='mysql-bin.000013',
->master_log_pos=106;
QueryOK,0rowsaffected(0.02sec)
mysql>startslave;
QueryOK,0rowsaffected(0.00sec)
mysql>showslavestatus\G;
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:192.168.240.131
Master_User:navy
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000013
Read_Master_Log_Pos:98
Relay_Log_File:alan-relay-bin.000002
Relay_Log_Pos:235
Relay_Master_Log_File:mysql-bin.000013
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:98
Relay_Log_Space:235
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
1rowinset(0.01sec)
ERROR:Noqueryspecified
当这个参数都为yes时,证明主从复制成功
Slave_IO_Running:YesSlave_SQL_Running:Yes
设置只读:
AlterdatabasedbnamesetREAD_ONLY
恢复读写:
AlterDATABASEdb_nameSETREAD_WRITE