MySQL主从同步原理 部署【转】,mysql主从原理部署

使用俩台mysql服务器实现AB,主从复制。
 
一、在主MASTER服务器配置
 
MASTER  172.16.1.3
BACKUP 172.16.1.2
 
 1、编辑my.cnf文件
 #在原有基础上添加这俩行
 
[[email protected]
~]# cat /etc/my.cnf
[mysqld]
log-bin=/mysql/bin    #开启binlog
server-id=1               #配置不和另一台重复就行
2、重启服务
[[email protected]
~]# service mysqld restart
停止MySQL:[确定]
启动MySQL:[确定]
3、授权用户
mysql> grant replication slave on *.* to
[email protected]
identified by ‘123456’
[[email protected]
~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77-log Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>
mysql> grant replication slave on *.* to
[email protected]
identified by ‘123456’;
Query OK, 0 rows affected (0.15 sec)
mysql>
4、在B服务器测试是否可以登录
[[email protected]
~]# mysql -uzhaoyun -p123456 -h172.16.1.3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights
reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free
software,
and you are welcome to modify and redistribute it under the GPL v2
license
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input
statement.
mysql> show grants ;
+—————————————————————————————————-+
| Grants for
[email protected]                                                                    
|
+—————————————————————————————————-+
| GRANT REPLICATION SLAVE ON *.* TO ‘zhaoyun’@’172.16.1.2’ IDENTIFIED
BY PASSWORD ‘565491d704013245’ |
+—————————————————————————————————-+
1 row in set (0.00 sec)
mysql>
5、查看master的状态
mysql> show master status ;
+————+———-+————–+——————+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————+———-+————–+——————+
| bin.000001 |      315  |              |                  |
+————+———-+————–+——————+
1 row in set (0.00 sec)
 
file字段是master的binlog文件名,position是binlog的节点。
二、配置BACKUP
1、编辑配置文件my.cnf,添加4行。
[mysqld]
server-id=2
master-host=172.16.1.3    #MASTER服务器的ip地址
master-user=zhaoyun      #连接MASTER服务器的用户名
master-password=123456  #密码
2、重启服务
[[email protected]
~]# service mysqld restart
Stopping mysqld:  [  OK  ]
Starting mysqld:  [  OK  ]
3、重启服务后会在数据库目录下生成几个文件
[[email protected]
mysql]# ls
         ib_logfile1  mysqld-relay-bin.000001  mysqld-relay-bin.index 
test
ibdata1      master.info   mysql.sock
ib_logfile0  mysql         relay-log.info
[[email protected]
mysql]# pwd
/var/lib/mysql
mysqld-relay-bin.000001  #binload文件,从master复制而来
mysqld-relay-bin.index   #binload的信息
master.info      #master信息
 relay-log.info   #中继日志信息
4、查看slave的状态
[[email protected]
~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> show slave status \G ;
*************************** 1. row
***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 172.16.1.3
                Master_User: zhaoyun
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000001
        Read_Master_Log_Pos: 315
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 453
      Relay_Master_Log_File: mysqld-bin.000001
           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: 315
            Relay_Log_Space: 453
            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
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
#这个是主服务的binlog文件的状态,如果出现IO是NO的话,需检查这俩个文件的状态。
      Master_Log_File: mysqld-bin.000001
       Read_Master_Log_Pos: 315
 
 #这俩条是slave的IO进程,和SQL进程的状态,AB复制的服务只有都为yes时才可用。
 
    Slave_IO_Running: YES
   Slave_SQL_Running: YES
#IO进程为NO可以将BACKUP的数据文件删除,重启服务重新同步就行了。
 
5、到现在配置基本完成
 
三、创建一个表进行测试,是否同步成功。
1、在master上创建。
mysql> create database master ;
Query OK, 1 row affected (0.00 sec)
mysql> use master
Database changed
mysql> create table master(id int,name char(5));
Query OK, 0 rows affected (0.04 se
2、在backup查看
[[email protected]
~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.77 Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> show database ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near ‘database’ at line 1
mysql> show databases ;
+——————–+
| Database           |
+——————–+
| information_schema |
| master             |
| mysql              |
| test               |
+——————–+
4 rows in set (0.00 sec)
mysql> use master
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables ;
+——————+
| Tables_in_master |
+——————+
| master           |
+——————+
1 row in set (0.00 sec)
mysql>
可以看到数据已经同步过来了。到此实验成功。
 
故障排除:
 
IO 等于NO :
需要检查节点和binlog文件名是否和在master看到的一致,如果不一致可以手动改写
命令
先停止slave服务
mysql>slave stop;
mysql>change master to
master_log_file=”在master看到的binlog文件名”;
mysql>change master to master_log_pos=100;
这个数字是在master看到的。
mysql>slave start ;
mysql> show master status ;
+——————-+———-+————–+——————+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| mysqld-bin.000001 |      507 |              |                  |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)
SQL等于NO,可以试着删除几个文件重启服务重新同步
mysqld-relay-bin.000001  #binload文件,从master复制而来
mysqld-relay-bin.index   #binload的信息
master.info      #master信息
 relay-log.info   #中继日志信息
本文出自 “技术交流” 博客

MySQL主从同步原理 部署【转】,mysql主从原理部署

一.主从的作用:
1.可以当做一种备份方式
2.用来实现读写分离,缓解一个数据库的压力
二.MySQL主从备份原理
master 上提供binlog ,
slave 通过 I/O线程从 master拿取 binlog,并复制到slave的中继日志中
slave 通过 SQL线程从 slave的中继日志中读取binlog ,然后解析到slave中
部署主从环境:主服务器:192.168.1.110(编译好的MySQL5.1版本的数据库)
从服务器:192.168.1.120(编译好的MySQL5.1版本的数据库)
(温馨提示:主和从数据库版本必须是一样。或者主库的数据库版本必须比从库高,不然会导致很多故障的发生。)
三:生产环境应用MySQL主从同步场景:
1.
一般用主库做为提供业务用户写操作(比如:在互联网上写一条微博,这时候就会写到mysql数据库的主库中)
2.
一般用从库做为提供业务用户读操作(比如:在互联网上,我想看一条微博,这时候里面提供数据就是MySQL数据库的从库中。)
(1)在主服务器(192.168.1.110)上操作。
[[email protected]
~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:5E:6F:A7
inet addr:192.168.1.110 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe5e:6fa7/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:141354 errors:0 dropped:0 overruns:0 frame:0
TX packets:140807 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:142083379 (135.5 MiB) TX bytes:17815696 (16.9 MiB)
Interrupt:193 Base address:0x2000
[[email protected]
~]# vi /etc/my.cnf
[mysqld]在mysqld下添加以上两行。
server-id = 1
log-bin= Andy -bin
[[email protected]
~]# /etc/init.d/mysqld restart
Shutting down MySQL[ OK ]
Starting MySQL.[ OK ]
[[email protected]
~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.44 Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input
statement.
mysql> show master status;
+——————–+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————–+———-+————–+——————+
| Andy-bin.000001 | 106 | | |
+——————–+———-+————–+——————+
1 row in set (0.00 sec)
请记住:File里的Andy-bin.000001 和 Position 106 。
Mysql> grant replication slave on *.* to ‘python’@’192.168.1.%’
identified by ‘123456’;
mysql> quit
Bye
在从服务器(192.168.1.120)上操作:
[[email protected]
~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:2B:8E:D2
inet addr:192.168.1.120 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe2b:8ed2/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:180290 errors:0 dropped:0 overruns:0 frame:0
TX packets:146169 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:223411069 (213.0 MiB) TX bytes:15504427 (14.7 MiB)
Interrupt:193 Base address:0x2000
[[email protected]
~]# vi /etc/my.cnf
把server-id = 1 改为:server-id = 2
然后重启Mysql服务:[[email protected]
~]# /etc/init.d/mysqld restart
Shutting down MySQL……..[ OK ]
Starting MySQL.[ OK ]
[[email protected]
~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.44 Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input
statement.
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.1.110′,
MASTER_PORT=3306, MASTER_USER=’python’, MASTER_PASSWORD=’123456′,
MASTER_LOG_FILE=’ Andy-bin .000001′, MASTER_LOG_POS=106;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.110
Master_User: python
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Andy-bin.000001
Read_Master_Log_Pos: 106
Relay_Log_File: Andy-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: Andy-bin.000001
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: 106
Relay_Log_Space: 410
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.01 sec)
如果: Slave_IO_Running: Yes (主从I/O正确)
Slave_SQL_Running: Yes(主从进程正确)

部署【转】,mysql主从原理部署
一.主从的作用: 1.可以当做一种备份方式
2.用来实现读写分离,缓解一个数据库的压力…

演示一下在MySQL下搭建多主一从的过程。

一、在主MASTER服务器配置 MASTER 172.16.1.3 BACKUP 172.16.1.2
1、编辑my.cnf文件 #在原有基础上添加…

建立授权账号

分别在3306和3307上面建立授权账号

修改从库存储方式

修改3308的master-info和relay-info方式,从文件存储改为表存储。

执行change master to

登录slave进行同步操作,分别change master两台服务器,后面以for channel
‘channel_name’区分

mysql> change master to
master_host=’192.168.24.129′,master_user=’backup’,master_port=3306,master_password=’backup’,master_log_file=’mysql-bin.000006′,master_log_pos=154
for channel ‘master_1’;

Query OK, 0 rows affected, 2 warnings (0.07
sec)

mysql> change master to
master_host=’192.168.24.129′,master_user=’backup’,master_port=3307,master_password=’backup’,master_log_file=’mysql-bin.000007′,master_log_pos=154
for channel ‘master_2’;

Query OK, 0 rows affected, 2 warnings (0.04
sec)

3306:

登录数据库:

[root@localhost 3306]# mysql -uroot -poldboy123 -S
/tmp/mysql3306.sock

锁表:

mysql> flush tables with read lock;

状态点:

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000006 |      154 |             
|                  |                   |

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

另开窗口开始导数据:

[root@localhost tmp]# mysqldump -uroot -poldboy123
-S /tmp/mysql3306.sock -F -R -x –master-data=2 -A –events|gzip
>/tmp/dockerwy.sql.gz

在此查看状态点两个要保持一致,否则表没有锁住

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000007 |      154 |             
|                  |                   |

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

解锁表:

mysql> unlock tables;

3307:

登录3307数据库: 

[root@localhost 3307]# mysql -uroot -poldboy123 -S
/tmp/mysql3307.sock

锁表:

mysql>flush tables with read lock;

查看状态点:

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000007 |      154 |             
|                |                   |

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

另开窗口导数据:

[root@localhost 3307]# mysqldump -uroot
-poldboy123 -S /tmp/mysql3307.sock -F -R -x –master-data=2 -A
–events|gzip >/tmp/dockerwy_2.sql.gz

从新查看状态点:

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File| Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000008 |      154 |             
|                  |                   |

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

解锁表:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

从库操作