Mysql master-slave synchronization and linuxmysql master-slave synchronization in Linux

Mysql master-slave synchronization and linuxmysql master-slave synchronization in Linux

I. test environment preparation:

Master: CentOS release 5.9 x86_64

IP: 10.45.172.40

Mysql Ver 14.12 Distrib 5.0.95

Slave database CentOS release 5.9 x86_64

IP: 10.45.172.37

Mysql Ver 14.12 Distrib 5.0.95,

Test whether to install mysql: service mysql restart or servicemysqld restart.

 

Install mysql:

Download the following installation package from the official websiteHttp://dev.mysql.com/downloads/mysql#downloads

Rpm-Ivh MySQL-server-5.5.25a-1.rhel5.x86_64 MySQL-client-5.5.25a-1.rhel5.x86_64 MySQL-devel-5.5.25a-1.rhel5.x86_64

Set/usr/bin/mysqladmin-u root password 'rootadmin'

 

 

2. operations on the master database

1. view the cnf file in the/usr/share/mysql directory of the master database server.

# Ll/usr/share/mysql/*. cnf

 

The. cnf documentation is as follows:

My-small.cnf memory is less than or equal to 64 M, providing only a small number of database services

The my-medium.cnf exists between 32M--64M and is used with other services, such as web

My-large.cnf memory has m mainly to provide database services

My-huge.cnf memory has 1G to 2G, mainly provide database services

My-innodb-heavy-4G.cnf memory 4 GB, mainly to provide a large load Database Service (General servers use this)

 

2. copy the file to/etc and change it to my. cnf.

# Cp/usr/share/mysql/my-innodb-heavy-4G.cnf/etc/my. cnf

 

3. modify my. cnf

# Vi/etc/my. cnf modify the following parameters:

Server-id = 1

Log-bin = mysql-bin

Binlog-do-db = test # database to be synchronized. If you want to synchronize multiple databases, You need to configure multiple rows.

Restart After Configuration:

Service mysql restart

 

4. Create a database account for synchronization

Mysql> grant replication slave on *. * to 'testuser' @ '10. 45.172.37 'identified by 'test123 ';

Query OK, 0 rows affected (0.00 sec)

 

5. Lock the master database table and stop data updates.

Mysql> flush tables with read lock;

 

6. Display and record master database information

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysql-bin.000001 | 566 |

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

1 row in set (0.00 sec)

 

Record the above log name mysql-bin.000001 and location 566, which will be used in library Configuration

 

7. Back up all database files and copy them to the slave database server.

Run the following command on the slave database:

Cd/var/lib/mysql

Mkdir/tmp/bak

Mv */tmp/bak

After the backup, copy it from the master database:

Scp-r root@10.45.172.40:/var/lib/mysql /*./

8. Unlock all tables in the master database after the database is copied.

# Unlock tables;

Iii. Slave database operations

1. modify the configuration file:

# Vi/etc/my. cnf

Server-id = 2 change this id number to 2

Log-bin = mysql-bin. If this option is enabled, disable it.

Master-host = 10.45.172.40 configure the IP address of the master database

Master-user = testuser account used for synchronization

Master-password = test123 account password used for synchronization

Master-port = 3306 port Number of the database to be synchronized

2. Adjust Permissions

# Chown-R mysql: mysql/var/lib/mysql

 

3. Restart the mysql service.

# Service mysql restart

 

4. manual synchronization

Mysql> slave stop

Mysql> CHANGE MASTER

MASTER_HOST = '10. 45.172.40 ',

MASTER_USER = 'testuser ',

MASTER_PASSWORD = 'test123 ',

MASTER_PORT = 3306,

MASTER_LOG_FILE = 'mysql-bin.000001 ',

MASTER_LOG_POS = 471,

MASTER_CONNECT_RETRY = 60;

5. Start slave Database

Mysql> slave start;

6. Check whether the slave database is properly synchronized.

Mysql> show slave status \ G;

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

Slave_IO_State: Waiting for master to sendevent

Master_Host: 10.45.172.40

Master_User: testuser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 471

Relay_Log_File: mysqld-relay-bin.000002

Relay_Log_Pos: 235

Relay_Master_Log_File: mysql-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: 471

Relay_Log_Space: 235

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)

 

Iv. Test

1. master database execution

Mysql> use test

Database changed

Mysql> create table tt (id int, namevarchar (20 ));

Query OK, 0 rows affected (0.00 sec)

 

Mysql> insert into tt values (1, 'jhp ');

Query OK, 1 row affected (0.00 sec)

 

2. Check the slave database for synchronization:

Mysql> select * from test. tt;

+ ------ +

| Id | name |

+ ------ +

| 1 | jhp |

+ ------ +

1 row in set (0.00 sec)