MySQL Master-Slave synchronization sub-database sub-table synchronization

First, the installation of MySQL database

Install MySQL database on master and slave, respectively

1.1 Installing the related packages
1.1.1 CMake Software
cd/home/oldboy/tools/
Tar XF cmake-2.8.8.tar.gz
CD cmake-2.8.8
./configure
#CMake has bootstrapped. Now run Gmake.
Gmake
Gmake Install
Cd.. /

1.1.2 Dependency Packages
Yum Install Ncurses-devel-y

1.2 Starting MySQL Installation
1.2.1 Creating Users and Groups
Groupadd MySQL
Useradd mysql-s/sbin/nologin-m-G MySQL

1.2.2 Decompression compiled MySQL
Tar zxf mysql-5.5.32.tar.gz
CD mysql-5.5.32
CMake. -dcmake_install_prefix=/application/mysql/mysql-5.5.40 \
-dmysql_datadir=/application/mysql/mysql-5.5.40/data \
-dmysql_unix_addr=/application/mysql/mysql-5.5.40/tmp/mysql.sock \
-ddefault_charset=utf8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI \
-DEXTRA_CHARSETS=GBK,GB2312,UTF8,ASCII \
-denabled_local_infile=on \
-dwith_innobase_storage_engine=1 \
-dwith_federated_storage_engine=1 \
-dwith_blackhole_storage_engine=1 \
-dwithout_example_storage_engine=1 \
-dwithout_partition_storage_engine=1 \
-dwith_fast_mutexes=1 \
-dwith_zlib=bundled \
-denabled_local_infile=1 \
-dwith_readline=1 \
-dwith_embedded_server=1 \
-dwith_debug=0

#--Build files has been written to:/home/oldboy/tools/mysql-5.5.32
Tip, there are many options to configure at compile time, refer to the end of the appendix or official documentation:
Make
#[100%] Built Target my_safe_process
Make install
Ln-s/application/mysql-5.5.32//application/mysql
If there is no error in the above operation, the installation of the MYSQL5.5.32 software CMake mode is successful.

1.3 After you install the database successfully

1.3.1 Modify the MySQL installation directory permissions, all modified to MySQL

Chown-r Mysql:mysql/application/mysql

1.3.2 Initializing the database

Cd/application/mysql/scripts

[Email protected] scripts]#/mysql_install_db--basedir=/application/mysql--datadir=/data/mysql--user=mysql

1.3.3 Replace the configuration file under/etc/and the startup file for the database

/application/mysql/support-files

Cp-r my-small.cnf/etc/my.cnf

Cp-r Mysql.server/etc/init.d/mysql

1.4 Starting the database

/etc/init.d/mysql start

/etc/init.d/mysql stop

/etc/init.d/mysql restart

Second, MySQL database sub-Library table synchronization

2.1master configuration file

Log-bin=mysql-bin

Binlog_format=row

Server-id = 15

Binlog-do-db=sales

/etc/init.d/mysql Restart (restart database)

2.2slave configuration file

Server-id = 25

Relay-log=relay-bin
Read-only = 1
REPLICATE-DO-DB = Sales

Replicate-do-db =user_info

Replicate-ignore-db = Information_schema
replicate-ignore-db = MySQL
Replicate-ignore-db = User_info

Replicate-wild-do-table =sales.story//single table for the database to be synchronized

/etc/init.d/mysql Restart (restart database)

2.3 Create a synced user on master
Grant replication client,replication Slave on * * to [e-mail protected] ' 172.27.1.% ' identified by ' password ';

Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000003
position:150
Binlog_do_db:sales,user_info
binlog_ignore_db:
Executed_gtid_set:
1 row in Set (0.00 sec)

ERROR:
No query specified

2.4 Execute the following statement on the slave, synchronizing to master

Change Master to master_host= ' 172.27.1.12 ' (Main library IP address), master_user= ' rep ', master_password= ' financial ', master_log_file= ' mysql-bin.000003 ', master_log_pos=150;

Start slave (start from library)

Stop slave (stop from library)

Reset slave (reset from library)

After starting the database, review the status of slave as follows:

Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.27.1.12
Master_user:rep
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:150
relay_log_file:relay-bin.000002
relay_log_pos:269
relay_master_log_file:mysql-bin.001603
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:sales
Replicate_ignore_db:information_schema,mysql,user_info
Replicate_do_table:
Replicate_ignore_table:
Replicate_Wild_Do_Table:sales.story
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:150
relay_log_space:419
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:15
1 row in Set (0.00 sec)

ERROR:
No query specified

The above means that the database master and slave are configured so that you can configure a single table in the same library

Test:

1. Create the database sales in master and slave respectively (you need to create the database manually in row mode).

2.use in the sales library, create a table on master

CREATE TABLE User_info (
PersonID int,
LastName varchar (255),
FirstName varchar (255),
Address varchar (255),
City varchar (255)
);

3. Create a table on master

CREATE TABLE Story (
PersonID int,
LastName varchar (255),
FirstName varchar (255),
Address varchar (255),
City varchar (255)
);

To view the slave synchronization status:

A. Story Table synchronization success

B. User_info table synchronization is unsuccessful

MySQL database single library, single-table synchronization is successful.

If it is only a library synchronization, it is removed from the slave configuration file:

Replicate-wild-do-table =sales.story//single table for the database to be synchronized

In single-table synchronization when the master----(high version 5.6.2 and above) appears (lower version 5.6.2 below) slave

After starting slave, the following error is reported:

[ERROR] Slave I/o: Got fatal error 1236 from master If reading data from binary log: ' Slave can not handle replication events WI Th the checksum that master was configured to log;

after querying the data found that: In the 5.6.2, a new parameter binlog_checksum, and in 5.6.6 and later versions, the value of the parameter from none to Crc32,master to 5.6.30 case, naturally have the option parameter ( events corrupt, so this option parameter appears. ), and its value is CRC32, but slave is 5.5.32, the option parameter does not exist at this time.

"solution":
Execute directly on master, you can:

1. mysql> SET  global binlog_checksum = none

also add to the [mysqld] configuration file to avoid restarting the failure:

1. [Mysqld]

2. Binlog-checksum = None

Note: Before the parameter value has not been modified Binlog, it seems to be unable to be received by IO thread, so the previous binlog may need to be manually executed to complement the data.
events in the modified Binlog can be transferred correctly to the slave.

This article is from the "Cat" blog, please be sure to keep this source http://yanruohan.blog.51cto.com/9740053/1923245

MySQL Master-Slave synchronization sub-database sub-table synchronization