MYSQL5.1 bidirectional master-Slave deployment

Two units MySQL database Server master and slave,master primary server,slave as slave server, initial state,master and The data in the slave is the same, and when the data in master changes,the slave changes accordingly, making master and slave Synchronization of data information to achieve the purpose of backup.

Points:

The medium responsible for transmitting various modification actions in the main and from the server is the binary change log of the primary server, which records the various modifications that need to be transmitted to the slave server. Therefore, the primary server must activate the binary logging feature. The slave must have sufficient permission to connect to the primary server and request that the primary server transfer the binary change log to it.

Lab Environment:

MASTER:192.168.127.120---Master

slave:192.168.127.121---Preparation

"Master192.168.127.120 Configuration"

my.cnf{

Symbolic-links=0

Server-id=1

Slave-skip-errors = All

Auto_increment_increment = 2

Auto_increment_offset = 2

Default-storage-engine = INNODB

Event_scheduler = On

Skip-external-locking

Local_infile=0

master-host=192.168.127.121

Master-user =slave

Master-password=radiusslave

master-port=3306

Log-bin = Mysql-bin

Binlog_format = Mixed

Expire_logs_days = 7

Relay-log=mysqld-relay-bin

Innodb_buffer_pool_size = 2G

Innodb_additional_mem_pool_size = 32M

Innodb_log_file_size = 256M

Innodb_log_buffer_size = 16M

Innodb_flush_log_at_trx_commit = 0

Innodb_lock_wait_timeout = 20

Innodb_thread_concurrency = 16

Innodb_flush_method = O_direct

innodb_max_dirty_pages_pct = 90

Query_cache_type = 0

Query_cache_size = 256M

Lower_case_table_names=1

Skip-name-resolve

}

"Master192.168.127.121 Configuration"

my.cnf{

Symbolic-links=0

# # # Add

server-id=2

Slave-skip-errors = All

Auto_increment_increment = 2

Auto_increment_offset = 2

Default-storage-engine = INNODB

Event_scheduler = On

Skip-external-locking

Local_infile=0

#master-host=192.168.127.120

#master-user =slave

#master-password=radiusslave

#master-port=3306

Log-bin = Mysql-bin

Binlog_format = Mixed

Expire_logs_days = 7

Relay-log=mysqld-relay-bin

Innodb_buffer_pool_size = 2G

Innodb_additional_mem_pool_size = 32M

Innodb_log_file_size = 256M

Innodb_log_buffer_size = 16M

Innodb_flush_log_at_trx_commit = 0

Innodb_lock_wait_timeout = 20

Innodb_thread_concurrency = 16

Innodb_flush_method = O_direct

innodb_max_dirty_pages_pct = 90

Query_cache_type = 0

Query_cache_size = 256M

Lower_case_table_names=1

Skip-name-resolve

}

"Master192.168.127.120 Configuration"

mysql> CREATE USER ' slave ' @ '% ' identified by ' slave123 ';

Mysql> GRANT All privileges on * * to ' slave ' @ ' localhost ' identified by ' slave123 ';

Mysql> GRANT All privileges on * * to ' slave ' @ '% ' identified by ' slave123 ';

mysql> flush Privileges;

Mysql>quit

Mysql>show grants for ' slave ' @ '% ';

-----Query the binary log file number that exists in the Master library. Data synchronization is implemented using this log file.

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000001 |              106 |                  | |

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

1 row in Set (0.00 sec)

Mysql> show binary logs;

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

| Log_name | File_size |

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

|       mysql-bin.000001 | 106 |

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

1 row in Set (0.00 sec)

Mysql>

"slave:192.168.127.121" Configuration Master

mysql> stop Slave;

mysql> Change Master to master_host= ' 192.168.127.120 ', master_user= ' slave ', master_password= ' slave123 ', Master_log _file= ' mysql-bin.000001 ', master_log_pos=106;

5.6 Version: master_port=38306

show slave status\g; View Configuration Results

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.127.120

Master_user:slave

master_port:3306

Connect_retry:10

master_log_file:mysql-bin.000001

read_master_log_pos:106

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:yes

Replicate_do_db:mysql

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

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

Mysql>

Slave_io_running:yes

Slave_sql_running:yes This 2 item display is configured successfully

"Slave 192.168.127.121 Configuration"

mysql> CREATE USER ' slave ' @ '% ' identified by ' slave123 ';

Mysql> GRANT All privileges on * * to ' slave ' @ ' localhost ' identified by ' slave123 '

Mysql> GRANT All privileges on * * to ' slave ' @ '% ' identified by ' slave123 '

mysql> flush Privileges;

Mysql>quit

Mysql>show grants for ' slave ' @ '% ';

-----Query the binary log file number that exists in the Master library. Data synchronization is implemented using this log file.

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000005 |              106 |                  | |

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

1 row in Set (0.00 sec)

Mysql> show binary logs;

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

| Log_name | File_size |

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

|       mysql-bin.000001 | 125 |

|       mysql-bin.000002 | 125 |

|       mysql-bin.000003 | 125 |

|       mysql-bin.000004 | 125 |

|       mysql-bin.000005 | 106 |

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

5 rows in Set (0.00 sec)

"master:192.168.127.121" Configuration Master

mysql> stop Slave;

mysql> Change Master to master_host= ' 192.168.15.92 ', master_user= ' slave ', master_password= ' slave123 ', Master_log_ File= ' mysql-bin.000009 ', master_log_pos=120;

5.6 Version: master_port=38306

show slave status\g; View Configuration Results

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.127.121

Master_user:slave

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000005

read_master_log_pos:106

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:251

relay_master_log_file:mysql-bin.000005

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

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

Mysql>

Slave_io_running:yes

Slave_sql_running:yes This 2 item display is configured successfully

mysql> Reset Slave;

mysql> stop Slave;

mysql> start slave;

The location of the refresh log file will be +1

Flush logs;

Create a Nodb library in the primary database, workers_info the table for testing

CREATE DATABASE nodb DEFAULT CHARACTER SET UTF8;

Workers_info

Class_info

CREATE TABLE ' Class_info ' (

ID Int (11),

Workername varchar () not NULL,

Salary int (11),

Email varchar (30),

Department varchar (30)

);

INSERT into Class_info values (1, ' jeck333 ', ' a ', ' [email protected] ', ' dep ');

View the slave database, create a library in the master library, build a table, insert data, and be synced to slave

This article is from the "Silent Dialogue" blog, please be sure to keep this source http://chbinmile.blog.51cto.com/6085145/1878936

MYSQL5.1 bidirectional master-Slave deployment