MySQL Master-Slave synchronization

main 192.168.20.63 duwers63.space

from 192.168.20.60 duwers60.space

Close iptables selinux

Consistent master-Slave database version

Topology

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/86/6B/wKioL1e-ahajxhdLAAByFriC3Ao718.jpg "title=" Master-slave synchronization. jpg "alt=" wkiol1e-ahajxhdlaabyfric3ao718.jpg "/>

Main Library Configuration "192.168.20.63"View version

[Email protected] ~]# mysql-uroot-p123456

Mysql> Show variables like '%version% '; # View version

+-------------------------+---------------------+

| variable_name | Value |

+-------------------------+---------------------+

| protocol_version | 10 |

| Version | 5.1.73-log |

| version_comment | Source Distribution |

| Version_compile_machine | x86_64 |

| Version_compile_os | Redhat-linux-gnu |

+-------------------------+---------------------+

5 rows in Set (0.01 sec)

Create a library that needs to be synchronized

mysql> CREATE DATABASE Duwers63;

Mysql> Use Duwers63

Mysql> CREATE TABLE test1 (id int);

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Duwers63 |

| MySQL |

| Ucenter |

+--------------------+

4 rows in Set (0.00 sec)

StopMysqlService

[[Email protected] ~]# service mysqld stop

Modifying a configuration file

[Email protected] ~]# VIM/ETC/MY.CNF

Add to

Log-bin=mysqllog # Open binary log

SERVER-ID=63 # Native database identity

BINLOG-DO-DB=DUWERS63 # binary to synchronize the db

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommendedto prevent assorted security risks

Symbolic-links=0

Log-bin=mysqllog

server-id=63

Binlog-do-db=tree

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

[Email protected] ~]#

RestartMysqlService

[[Email protected] ~]# service mysqld start

[Email protected] ~]# mysql-uroot-p123456

Authorized Sync Account

mysql> grant replication Slave on * * [email protected] identified by "123456";

View the main library status

Mysql> Show master status;

+-----------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-----------------+----------+--------------+------------------+

|      mysqllog.000001 | 257 |                  Tree | |

+-----------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

View binary Log status

[Email protected] ~]# ls/var/lib/mysql/

Duwers63 ibdata1 ib_logfile0 ib_logfile1 mysql mysqllog.000001 mysqllog.index mysql.sock ucenter

[Email protected] ~]#

ensure consistency of the first two databases in a synchronous replication

[Email protected] ~]# mysqldump-u root-p123456-a >all.sql

[Email protected] ~]# scp-p 44968 [email protected]:/root

from server Configuration "192.168.20.60"

[[Email protected] ~]# service mysqld start

Test Connectivity

[Email protected] ~]# mysql-u slave-h192.168.20.63-p123456-a

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

+--------------------+

1 row in Set (0.00 sec)

Import the database and maintain consistency with the primary database

[Email protected] ~]# mysql-u root-p</root/all.sql

Modifying a configuration file

[Email protected] ~]# VIM/ETC/MY.CNF

Add to

Server-id=60

master-host=192.168.20.63

Master-user=slave

master-password=123456

Restart Service

[Email protected] ~]# service mysqld restart

Check the status of the replication feature from the server

[Email protected] ~]# mysql-u root-p123456

Mysql> Show Slave status \g

1. row***************************

Slave_io_state:waiting Formaster to send event

master_host:192.168.20.63

Master_user:slave

master_port:3306

Connect_retry:60

master_log_file:mysqllog.000001

read_master_log_pos:257

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:401

relay_master_log_file:mysqllog.000001

             slave_io_running:yes                               # io communication yes Description successful installation from server

Slave_sql_running:yes                              # responsible for their own slave MySQL Process Yes description successful from server installation

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:257

relay_log_space:557

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)

synchronization of test dataon the primary serverMasterWrite data on

Mysql> Use Duwers63

Mysql> Show tables;

+--------------------+

| Tables_in_duwers63 |

+--------------------+

| Test1 |

+--------------------+

1 row in Set (0.00 sec)

mysql> INSERT INTO test1 values (1);

on the slave serverSlaveRead the data

Mysql> Use Duwers63

Mysql> select * from Test1;

+------+

| ID |

+------+

| 1 |

+------+

1 row in Set (0.00 sec)

This article is from the "duwers" blog, make sure to keep this source http://duwers.blog.51cto.com/7376225/1842374

MySQL Master-Slave synchronization