MySQL database master-Slave construction

Main Library server: 192.168.1.100

From the library server: 192.168.1.101

Install MySQL on both servers,

Yum-y install MySQL mysql-server mysql-devel

1. Primary service Settings
   

• Set Password:/usr/bin/mysqladmin-u root password ' passwd '.

• Edit configuration file: vi/etc/my.cnf

Add the following content:

Log-bin=mysql-bin Log Path

Server-id = 1 Specify the server ID

# binlog-do-db =cacti synchronizes the cacti library and, if not set, synchronizes all libraries by default.

#binlog-ignore-db =mysql synchronization of MySQL libraries is ignored, and all libraries are synchronized by default if not set.

Save exit.

• Add Authorization:

/usr/local/mysql/bin/mysql-uroot–p Enter passwd to enter the database.

under MySQL, enter the authorization command:

Grant Replication Slave on * * to [e-mail protected] ' 192.168.1.101 ' identified by ' passwd ';

grants access to the database from the host using the root user.

flush Privileges; Update the database.

• View the file and position values:

exit the MySQL interface to restart the database. Service mysqld Restart

Enter the command show master status; View the values of file and position under record, slave end to use.

The following example is executed:

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|       mysql-bin.000001 |              106 |                  | |

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

1 row in Set (0.00 sec)

You need to record mysql-bin.000001 and 98 here.

2 , from the server configuration:

• Set password,/usr/bin/mysqladmin-u root password ' passwd '

The password for root set above is passwd. Here I also set to passwd

• Edit configuration file: vi/etc/my.cnf

Add the following content:

Server-id = 2 Specify the server ID

Be sure to change it, not the same as the value on master. Otherwise, change master does not succeed.

Save exit.

Restart Mysql,servicemysqld restart

• Specify master Master information:

Enter MYSQLL

Mysql–uroot–pmy7gadmin

Change master to master_host= ' 192.168.1.100 ', master_user= ' root ', master_password= ' passwd ', master_port=3306, Master_log_file= ' mysql-bin.000001 ', master_log_pos=106;

Specify the IP address, user name, password, port number (port number without quotation marks), log file, log pos (without quotation marks).

start slave

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

View Status: If the * * * area is yes, it is normal.

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:192.168.1.229

Master_user:root

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

Read_master_log_pos:98

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

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)

• Configured at this point: Switch to master Master to view the database using the command.

Mysql> showdatabases;

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

| Database |

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

|information_schema |

| MySQL |

| Test |

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

3 Rows in Set (0.00SEC)

Create a database

mysql> Create Databasenio;

Query OK, 1 rowaffected (0.00 sec)

Then switch to use the command from slave to see if the database is synchronized. As the following command:

Mysql> showdatabases;

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

| Database |

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

|information_schema |

| MySQL |

| NiO |

| Test |

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

4 rows in Set (0.00SEC)

You can see that it has been synced over. At this point, MySQL master configuration is complete.

Problems encountered

Slave_sql_running:no

1. The program may have been written on the slave

2. It is also possible that the transaction rollback is caused by the slave machine being reset.

This is typically caused by a transaction rollback:
Workaround:
mysql> slave stop;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> slave start;

MySQL database master-Slave construction