mysql主从同步配置

mysql主从同步开启后的iptables的设定难点

mysql复制主从集群搭建

不久前搭了个主从复制,中间出了点小标题,排查解决,记录下来

1
环境:
虚拟机:
OS:
centos6.5
Linux host2 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013
x86_64 x86_64 x86_64 GNU/Linux

服务器IP
192.168.18.66
192.168.18.67

DB:
mysql> select version();
+———–+
| version() |
+———–+
| 5.6.20 |
+———–+

2
主机:192.168.18.66
从机:192.168.18.67

3
修改主服务器配置,加多如下内容:
server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest

此时主服务器那一个布局文件/etc/my.cnf内容如下:
[client]
#password = system
#port = 3306
default-character-set=utf8

[mysqld]

server-id=10
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db=reptest

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500

[mysql]
default-character-set=utf8

4
修改从服务器配置
server-id=20
relay_log=mysql-relay-bin
read_only

那时从服务器配置文件内容如下:
[client]
#password=system
#port=3306
default-character-set=utf8

[mysqld]

server-id=20
relay_log=mysql-relay-bin
#read_only
#log_slave_updates=1

#master-host=192.168.18.66
#master-user=repl
#master-password=123
#master-port=3306
#master-connect-retry=60
#replicate_do_db=reptest
#replicate_ignore_db=mysql,information_schema,performance_schema

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
port=3306
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
max_connections=500

[mysql]
default-character-set=utf8

mysql复制的连锁参数可参看上边包车型大巴网页:

5
在主上扩展复制用户
mysql> grant replication slave on *.* to ‘repl’@’%’ identified by
‘123456’;
flush privileges;

192.168.18.67是从服务器,就透过repl用户密码为空来同步复制

mysql> select host,user,Repl_slave_priv from mysql.user where
user=’repl’;
+—————+——+—————–+
| host | user | Repl_slave_priv |
+—————+——+—————–+
| 192.168.18.67 | repl | Y |
+—————+——+—————–+
1 row in set (0.00 sec)

6
重启主从服务器:
停主,停从
mysqladmin -uroot shutdown -psystem
起从,起主
/etc/init.d/mysql start

[roo[email protected]
~]# /etc/init.d/mysql start
Starting MySQL.. SUCCESS!

7
导出主数据库数据,取快速照相
1)锁主库
flush tables with read lock;

2)
这一步比较主要,要切记File和Position值,在起从服务器上的slave线程时备用
mysql> show master status \G
*************************** 1. row
***************************
File: mysql-bin.000002
Position: 401
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)

3)
[[email protected]
~]# mysqldump -uroot -p reptest –triggers –routines –events >
/home/zxw/master_reptest.sql

顺便看一下,mysqldump的剧情如下:
[[email protected]
~]# ll /home/zxw/
total 4
-rw-r–r–. 1 root root 1910 Aug 25 13:50 master_reptest.sql
[[email protected]
~]# nl /home/zxw/master_reptest.sql
1 — MySQL dump 10.13 Distrib 5.6.20, for Linux (x86_64)
2 —
3 — Host: localhost Database: reptest
4 — ——————————————————
5 — Server version 5.6.20-log

6 /*!40101 SET
@[email protected]@CHARACTER_SET_CLIENT
*/;
7 /*!40101 SET
@[email protected]@CHARACTER_SET_RESULTS
*/;
8 /*!40101 SET
@[email protected]@COLLATION_CONNECTION
*/;
9 /*!40101 SET NAMES utf8 */;
10 /*!40103 SET
@[email protected]@TIME_ZONE
*/;
11 /*!40103 SET TIME_ZONE=’+00:00′ */;
12 /*!40014 SET
@[email protected]@UNIQUE_CHECKS,
UNIQUE_CHECKS=0 */;
13 /*!40014 SET
@[email protected]@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
14 /*!40101 SET
@[email protected]@SQL_MODE,
SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
15 /*!40111 SET
@[email protected]@SQL_NOTES,
SQL_NOTES=0 */;

16 —
17 — Table structure for table `tbldata`
18 —

19 DROP TABLE IF EXISTS `tbldata`;
20 /*!40101 SET @saved_cs_client = @@character_set_client */;
21 /*!40101 SET character_set_client = utf8 */;
22 CREATE TABLE `tbldata` (
23 `id` int(11) DEFAULT NULL
24 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
25 /*!40101 SET character_set_client = @saved_cs_client */;

26 —
27 — Dumping data for table `tbldata`
28 —

29 LOCK TABLES `tbldata` WRITE;
30 /*!40000 ALTER TABLE `tbldata` DISABLE KEYS */;
31 INSERT INTO `tbldata` VALUES (1),(2),(3);
32 /*!40000 ALTER TABLE `tbldata` ENABLE KEYS */;
33 UNLOCK TABLES;

34 —
35 — Dumping events for database ‘reptest’
36 —

37 —
38 — Dumping routines for database ‘reptest’
39 —
40 /*!40103 SET
[email protected]_TIME_ZONE
*/;

41 /*!40101 SET
[email protected]_SQL_MODE
*/;
42 /*!40014 SET
[email protected]_FOREIGN_KEY_CHECKS
*/;
43 /*!40014 SET
[email protected]_UNIQUE_CHECKS
*/;
44 /*!40101 SET
[email protected]_CHARACTER_SET_CLIENT
*/;
45 /*!40101 SET
[email protected]_CHARACTER_SET_RESULTS
*/;
46 /*!40101 SET
[email protected]_COLLATION_CONNECTION
*/;
47 /*!40111 SET
[email protected]_SQL_NOTES
*/;

48 — Dump completed on 2014-08-25 13:50:48

4)
解锁数据库
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

############################
###拷贝数据文件目录情势#####
############################
#其次种取主数据库快速照相的诀窍
#mysqladmin -uroot shutdown
#卷入数据库数据目录,比方数据目录是/data/dbdata:
#cd /data
#tar zcvf dbdata.tar.gz dbdata
#备份后就能够运行主服务器了:
#mysqld_safe –user=mysql &

8
在从服务器上苏醒主库快速照相
1)
在从库上成立数据库
mysql> create database reptest;
Query OK, 1 row affected (0.00 sec)
2)
拷贝备份脚本到从服务器
[[email protected]
~]# scp
[email protected]:/home/zxw/master_reptest.sql
/home/zxw/
3)
主库快速照相导入到从库
[[email protected]
~]# mysql -uroot -psystem reptest < /home/zxw/master_reptest.sql
Warning: Using a password on the command line interface can be
insecure.
4)
验证:
[[email protected]
~]# mysql -uroot -psystem
mysql> use reptest;
mysql>
mysql> show tables
-> ;
+——————-+
| Tables_in_reptest |
+——————-+
| tbldata |
+——————-+
1 row in set (0.00 sec)

mysql> select * from tbldata;
+——+
| id |
+——+
| 1 |
| 2 |
| 3 |
+——+
3 rows in set (0.00 sec)

mysql>

############################
###拷贝数据文件目录形式#####
############################
#备份文件形式的导入
#出于须求置换来主服务器的数量目录,先关闭服务:
#mysqladmin -uroot shutdown
#备份数据目录
#mv dbdata dbdata.bak
#解包从主服务器拷贝来的多少目录
#tar zxvf dbdata.tar.gz
#要确认保障文件的权限属主等装置没难题,dbdata目录应当是mysql:mysql用户具备。

9
1)
在从服务器上操作,连接主服务器开端联手数据:
mysql> Change master to Master_host = ‘192.168.18.66’, Master_port
= 3306, Master_user = ‘repl’, Master_password = ‘123456’,
Master_log_澳门金沙vip,file = ‘mysql-bin.000002’, Master_log_pos = 401;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql>
此处蕴含的音信有主机的地点和端口、主机提供的复制帐号、主机的binlog地方消息。Master_log_file和Master_log_pos是主服务器的快速照相消息(就是第7不第2小步看看的值),从服务器从该binlog的相应岗位上马从主服务器同步数据。

2)
早先从服务器线程就能够初步联手了:
start slave;
假使从服务器开首同步了,就能够在数据文件目录下找到2个公文master.info和relay-log.info。从服务器利用那2个文件来跟踪管理了稍稍master的binlog。
独家在基本服务器show
processlist查看连接,就可以观看repl用户的接连,可表明复制已经生效。

从:
mysql> show slave status \G
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.66
Master_User: usrep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1264
Relay_Log_File: mysql-relay-bin.000021
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
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: 1264
Relay_Log_Space: 1075
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: c03d6252-2a2f-11e4-9b48-000c291888ce
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for
the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

主:能够看到上面内容
mysql> show processlist \G
*************************** 2. row
***************************
Id: 11
User: usrep
Host: 192.168.18.67:48746
db: NULL
Command: Binlog Dump
Time: 179
State: Master has sent all binlog to slave; waiting for binlog to be
updated
Info: NULL
2 rows in set (0.00 sec)

从服务器:
数据文件中相关文件如下
[[email protected]
~]# ll /var/lib/mysql/
-rw-rw—-. 1 mysql mysql 128 Aug 28 11:32 master.info
-rw-rw—-. 1 mysql mysql 59 Aug 28 11:32 relay-log.info

-rw-rw—-. 1 mysql mysql 792 Aug 28 11:32 mysql-relay-bin.000020
-rw-rw—-. 1 mysql mysql 283 Aug 28 11:32 mysql-relay-bin.000021
-rw-rw—-. 1 mysql mysql 50 Aug 28 11:32 mysql-relay-bin.index

主服务器:
数据文件中相关文件如下
-rw-rw—-. 1 mysql mysql 1036 Aug 28 09:32 mysql-bin.000003
-rw-rw—-. 1 mysql mysql 1264 Aug 28 11:04 mysql-bin.000004
-rw-rw—-. 1 mysql mysql 76 Aug 28 09:32 mysql-bin.index

到此时就ok了

上边说一下在布署进度中相遇的主题素材:

问题1
1
刚搭完跑起来一看,有标题,Slave_IO_Running:
Connecting,IO线程链接主服务进程未有中标
mysql> show slave status \G;
*************************** 1. row
***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.18.66
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 401
Relay_Log_File: host2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
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: 401
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master
‘[email protected]:3306’

  • retry-time: 60 retries: 1
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
    Master_Server_Id: 0
    Master_UUID:
    Master_Info_File: /var/lib/mysql/master.info
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for
    the slave I/O thread to update it
    Master_Retry_Count: 86400
    Master_Bind:
    Last_IO_Error_Timestamp: 140825 14:29:05
    Last_SQL_Error_Timestamp:
    Master_SSL_Crl:
    Master_SSL_Crlpath:
    Retrieved_Gtid_Set:
    Executed_Gtid_Set:
    Auto_Position: 0
    1 row in set (0.00 sec)

问题1
2
查看日志有如下内容
[[email protected]
~]# tail -n 30 /var/lib/mysql/host2.err
2014-08-27 17:04:37 2384 [ERROR] Slave I/O: error connecting to master
‘[email protected]:3306’

  • retry-time: 60 retries: 1, Error_code: 2003
    2014-08-27 17:04:37 2384 [Warning] Slave SQL: If a crash happens this
    configuration does not guarantee that the relay log info will be
    consistent, Error_code: 0
    2014-08-27 17:04:37 2384 [Note] Slave SQL thread initialized, starting
    replication in log ‘mysql-bin.000003’ at position 120, relay log
    ‘./mysql-relay-bin.000001’ position: 4
    2014-08-27 17:05:12 2384 [Note] Error reading relay log event: slave
    SQL thread was killed
    2014-08-27 17:05:12 2384 [Note] Slave I/O thread killed while
    connecting to master
    2014-08-27 17:05:12 2384 [Note] Slave I/O thread exiting, read up to
    log ‘mysql-bin.000003’, position 120

问题1
3
在主上新建二个全权用户,在从上用这一个用户做复制,结果同样
主:
mysql> grant all on *.* to ‘usrep’@’%’ identified by ‘123456’;

mysql> Change master to Master_host = ‘192.168.18.66’, Master_port
= 3306, Master_user = ‘repl’, Master_password = ‘123456’,
Master_log_file = ‘mysql-bin.000002’, Master_log_pos = 401;
用usrep在从上起slave复制线程,难题仍旧

问题1
4
在主上mysql -uusrep -p直接登陆主数据库,成功。
在从上mysql -h 192.168.18.67 -uusrep -p登入主数据库,退步。
[[email protected]
~]# mysql -h 192.168.18.67 -uroot -psystem
Warning: Using a password on the command line interface can be
insecure.
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.18.66’
(113)

问题1
5
查看主的iptable
[[email protected]
~]# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all — anywhere anywhere state RELATED,ESTABLISHED
ACCEPT icmp — anywhere anywhere
ACCEPT all — anywhere anywhere
ACCEPT tcp — anywhere anywhere state NEW tcp dpt:ssh
REJECT all — anywhere anywhere reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
target prot opt source destination
REJECT all — anywhere anywhere reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

问题1
6
开发文件
/etc/sysconfig/iptables(该公文路线因操作系统而异),文件内容如下:
[[email protected]
~]# nl /etc/sysconfig/iptables
1 # Firewall configuration written by system-config-firewall
2 # Manual customization of this file is not recommended.
3 *filter
4 :INPUT ACCEPT [0:0]
5 :FORWARD ACCEPT [0:0]
6 :OUTPUT ACCEPT [0:0]
7 -A INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT
8 -A INPUT -p icmp -j ACCEPT
9 -A INPUT -i lo -j ACCEPT
10 -A INPUT -m state –state NEW -m tcp -p tcp –dport 22 -j ACCEPT
11 -A INPUT -j REJECT –reject-with icmp-host-prohibited
12 -A FORWARD -j REJECT –reject-with icmp-host-prohibited
13 COMMIT

编写该公文扩展一行,松手tcp的3306端口
-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
结果如下
[[email protected]
~]# nl /etc/sysconfig/iptables
1 # Firewall configuration written by system-config-firewall
2 # Manual customization of this file is not recommended.
3 *filter
4 :INPUT ACCEPT [0:0]
5 :FORWARD ACCEPT [0:0]
6 :OUTPUT ACCEPT [0:0]
7 -A INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT
8 -A INPUT -p icmp -j ACCEPT
9 -A INPUT -i lo -j ACCEPT
10 -A INPUT -m state –state NEW -m tcp -p tcp –dport 22 -j ACCEPT
11 -A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT
12 -A INPUT -j REJECT –reject-with icmp-host-prohibited
13 -A FORWARD -j REJECT –reject-with icmp-host-prohibited
14 COMMIT

重启iptable服务
[[email protected]
~]# /etc/init.d/iptables restart

查看现成iptables规则:
[[email protected]
~]# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all — anywhere anywhere state RELATED,ESTABLISHED
ACCEPT icmp — anywhere anywhere
ACCEPT all — anywhere anywhere
ACCEPT tcp — anywhere anywhere state NEW tcp dpt:ssh
ACCEPT tcp — anywhere anywhere state NEW tcp dpt:mysql
REJECT all — anywhere anywhere reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
target prot opt source destination
REJECT all — anywhere anywhere reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

问题1
7
再在从上运转slave复制线程,难题消除:
mysql> show slave status \G
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.66
Master_User: usrep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1264
Relay_Log_File: mysql-relay-bin.000021
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
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: 1264
Relay_Log_Space: 1075
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: c03d6252-2a2f-11e4-9b48-000c291888ce
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for
the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

那儿主从复制搭建完成,测试了足以健康运作。

问题2
想把从数据库搞成只读的,在陈设文件中加read_only参数及各样赋值和启动和停止从库n次,未有直达预期效应,依旧能够直接连从库开始展览增加和删除改。
此间特古怪,改日再试

不幸苏醒

主从分化步
只要基本同步出现了分歧,就要求重新推行主从复制。步骤和地点同样,只是简短了改变配置文件和创办用户的步子。
重新配置此前,须求在从服务器结束同步线程:stop slave;

从从服务器苏醒
若是主机挂了,能够把从服务器进步为主机,把主人服务器作为备机。
先在从服务器结束同步线程:
stop slave;
在从服务器上丰硕同步用户:
grant replication slave on *.* to
[email protected]’从服务器ip’
identified by ‘123456’;
flush privileges;
计划文件中my.cnf的server-id能够不改换,只要保险id不争执就行了。

接下来,按执照主人从复制的步调来实行操作。 


转发请著明出处:
blog.csdn.net/beiigang

如今搭了个主从复制,中间出了点小标题,排查解决,记录下来 1 情状:
虚拟机: OS: centos6.5 Linux host2 2.6.32-431.el6…

主host:www IP:192.168.9.124
从host:www1 IP:192.168.9.123

 

#下载mysql
wget

这两台在做mysql的中坚同步,小编的mysql服务器版本是5.6.10,mysql5.6事后做为主同步的时候,官方提出把master.info中的消息放在数据库中,具体地点在mysql数据库中的master.info表中

#安装mysql
tar xzf mysql-5.1.58-linux-i686-glibc23.tar.gz
useradd mysql
mv mysql-5.1.58-linux-i686-glibc23 /usr/local/mysql

 

chown -R root .
chown -R mysql data
cp support-files/my-medium.cnf /etc/my.cnf
bin/mysqld_safe –user=mysql &
cp support-files/mysql.server /etc/init.d/mysql.server
cd

mysql的主干同步的例证已经重重了,以往主要说说自身越过的主题材料,主从同步的装置很顺畅,简要说美素佳儿(Friso)下:

export PATH=$PATH:/usr/local/mysql/bin/ # 参加情状变量

 

 

在主数据库中实行sql

个别改动主从root密码
# mysql
mysql>update mysql.user set password=password(‘123456’) where
user=”root” and host=”localhost”;

 

www:
mysql> grant all privileges  on *.* to
[email protected]
identified by ‘123456’;   #授权192.168.9.123 登陆mysql
mysql> flush privileges;     #刷新权限表
mysql> exit;

flush tables with read lock;

在从服务器测试
www1
# mysql -u root -h 192.168.9.124 -p

 

www
mysql> FLUSH TABLES WITH READ LOCK;  (主库加锁)
笔录日志和偏移量
mysql> show master status ;
mysqldump -p –all-database –lock-all-tables >db.sql

查看主数据库binlog记录:

www1
scp -P
[email protected]:/root/db.sql
.

 

#########  修改从my.cnf ############
server-id=2
master-host=192.168.9.124
master-user=slave
master-password=123456
master-port=3306

show master status;

log-bin=mysql-bin

重启mysql
lsof -i:3306 |xargs kill -9
/usr/local/mysql/bin/mysqld_safe –user=mysql &

mysql -p < db.sql    # 导入db.mysql

 

www

mysql>unlock tables ;   (主库解锁)

mysql>show master status ;

www1

mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.9.124′,
MASTER_USER=’root’,

MASTER_PASSWORD=’123456′,MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=548; 
(后面log_file log_pos 参照主库的日记名称和偏移量)

mysql>  slave start ;

查状态

mysql> show master status;

mysql> show slave status;

mysql> show processlist ;

 

www

-A INPUT -s 192.168.9.123 -p tcp –dport 3306 -j ACCEPT
放行192.168.9.123连接3306端口

-A INPUT -p tcp –dport 22 -j ACCEPT #允许ssh

-A INPUT -j DROP  #闭门羹全数
作者 “xavier”

IP:192.168.9.124 从host:www1
IP:192.168.9.123 #下载mysql wget

 

备份主数据库:

 

1、能够对数据库打包:tar -zcvf /backup/mysql_dump.tar.gz /var/lib/mysql

 

2、可以用mysql的工具 mysqldump:mysqldump –user=root  -h 127.0.0.1
–databases test_repl –default-character-set=utf8 –lock-all-tables
> /backup/test_repl.sql

 

在从数据库上过来数据库

 

1、tar包的直接解压到mysql存放数据库的目录,修改文件为mysql的权位就能够