The MySQL master-slave synchronization configuration for CentOS

First, the host master and Salver both shut down the firewall iptables, execute the service iptables Stop command:

To set the selinux=disabled of the SELinux file:

Second, create a database

Log in to master machine and slave machine separately mysql:mysql–u root–p

Creating a database: Create databases Repl;

Build the log profile for MySQL replication in the/usr/local/mysql directory/usr/local/mysql/mysql-bin.log and grant permissions and change the owner of the file:

[[email protected] MySQL] touch mysql-bin.log

[[email protected] MySQL] chmod 755 mysql-bin.log

[[email protected] MySQL] chown-r mysql:mysql .

1, modify the master machine MySQL configuration file my.cnf, the file in/etc directory

Add the following fields to the [mysqld] configuration segment

Server-id=1

Log-bin=log

BINLOG-DO-DB=REPL//The database that needs to be synchronized, if there is no bank, that means synchronizing all the databases

Binlog-ignore-db=mysql//Ignored database

Add a sync account to the slave machine on the master machine
Grant Replication Slave on * * to ' repl ' @ ' 192.1681.220 ' identifiedby ' 123456 ';

Restart the master machine's MySQL service: Service mysqld restart

See log status with Show Master Status command

Mysql>show Master status;

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

| File    | Position | binlog_do_db | binlog_ignore_db |

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

|  log.000003|   98 |repl c43> |mysql c53> c63> |

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

1 row in Set (0.00 sec)

Add the Sync account for host Salver on host master:

mysql> grant replication Slave on * * to ' slave ' @ ' 192.168.31.137 ' identified by ' admin ';

Perform the following command to view:

Add the following fields to the [mysqld] configuration segment

Server-id=1

Log-bin=log

BINLOG-DO-DB=REPL//The database that needs to be synchronized, if there is no bank, that means synchronizing all the databases

Binlog-ignore-db=mysql//Ignored database

Add the following property entry in the file:

Log-bin =/usr/local/mysql/mysql-bin.log

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

Add a sync account to the slave machine on the master machine
Grant Replication Slave on * * to ' repl ' @ ' 192.1681.220 ' identifiedby ' 123456 ';

Restart the master machine's MySQL service: Service mysqld restart

S

Select User.host from Mysql.user

You can see the backup account and Setup complete. The following lock is read-only for database tables.

MySQL lock table read-only (other accounts log on to MySQL can not write table operations, to prevent the backup of the database after the main MySQL table update, resulting from inconsistent with the database content)

Mysql> flush tables with read lock;

E: View Status:

[[email protected] MySQL] mysql-u root-padmin-e "Show Master Status"

Note: The log name (mysql-bin.000001) of the file field is the log file required for the slave backup.

2. Modify the MySQL configuration file in the slave machine

Unlock database:

mysql> unlock tables;

Also add the following in the [Mysqld] field

server-id=2

Start Here

master-host=192.168.1.222

Master-user=repl

master-password=123456

master-port=3306

Master-connect-retry=60

To this end, these parameters cannot be configured in the database after 5.1 MySQL, can only command

REPLICATE-DO-DB=REPL//synchronization of the database, does not write the bank means that all databases are synchronized

Precautions (Error)
MySQL version starting from 5.1.7 does not support "master-host" similar parameters
Execute the following command from the library;
Change Master to master_host= ' Masterip ', master_user= ' slave ', master_password= ' slvaepass ';

Change Master to master_host= ' 10.171.244.109 ',

Master_user= ' slave ',

Master_password= ' admin ',

Master_log_file= "mysql-bin.000006",

master_log_pos=107;

Then restart the MySQL slave machine

Enter MySQL in the slave machine

Mysql>start slave;

Mysql>show slave statusg;
If the slave_io_running, slave_sql_running status is yes, the setting is successful.

Iv. problems that arise

Question 1 When I execute the start slave command, the system prompts

ERROR HY000: The server is not configured as slave; Fixin config file or with change MASTER to,

Executes show slave status, and the empty set (0.00 sec), wondering where the problem is

or question 2 slave_sql_running:no

Later found that the original slave has been turned on by default, to first turn off and then open

Execute slave stop;

Re-execute

Change Master tomaster_host= ' 192.168.1.222 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' Log.000003 ', master_log_pos=98;

then execute slave start;

Then execute show slave STATUSG

Shown below:

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

           < /c13>slave_io_state:waiting for Master to send event

            > master_host:192.168.1.222

          < c25> master_user:repl

          < c40> master_port:3306

          < c55> connect_retry:60

          < c68>master_log_file:log.000003

read_master_log_pos:98

           relay_log_file:mysqld-relay-bin.000002

          < c28> relay_log_pos:229

relay_master_log_file:log.000003

Sl Ave_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:

          < c43> last_errno:0

            > last_error :

          < c26> skip_counter:0

exec_master_log_pos:98

          < c47>relay_log_space:229

          < c58>Until_condition:none

          < c69> until_log_file:

            until_log_pos:0

master_ssl_allowed:no

master_ssl_ca_file :

master_ssl_ca_path :

          < c47>Master_ssl_cert:

master_ssl_ Cipher:

           master_ssl_key:

seconds_behind_master:0

1 row in Set (0.00 sec)

Shows the two lines in red, indicating the configuration was successful.

V. Test whether the master and slave server can synchronize

Create a new table above the primary server, must be under REPL data

Mysql> Use REPL

Database changed

Mysql> CREATE TABLE Test (ID Int,namechar (10));

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO test values (1, ' Zaq ');

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO test values (1, ' xsw ');

Query OK, 1 row Affected (0.00 sec)

Mysql> select * from test;

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

| ID |name |

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

| 1 |zaq |

| 1 | XSW |

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

2 rows in Set (0.00 sec)

Check to see if it's synced from the server.

mysql> use REPL;

Database changed

Mysql> select * from test;

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

| ID |name |

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

| 1 | Zaq |

| 1 | XSW |

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

2 rows in Set (0.00 sec)

First
Flush privileges;
And then
Show grants for ' slave ' @ ' 10.169.6.133 ';

The logon database encountered
Encounter ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)
Restart Service MySQL Restart

Error: Starting MySQL. The server quit without updating PID file ([failed]al/mysql/data/iz23xrasgttz.pid).
How to resolve:
Delete/etc/mysql folder
and modify the Mstart entry in the configuration file to comment out

Precautions (Error)
MySQL version starting from 5.1.7 does not support "master-host" similar parameters
Execute the following command from the library;
Change Master to master_host= ' Masterip ', master_user= ' slave ', master_password= ' slvaepass ';

Change Master to master_host= ' 10.171.244.109 ',

Master_user= ' slave ',

Master_password= ' admin ',

Master_log_file= "mysql-bin.000006",

master_log_pos=107;

Previous reference URL:


http://blog.csdn.net/sxhong/article/details/9212109 (set MySQL root password under CentOS)

http://blog.csdn.net/limingzhong198/article/details/20578821 (master-slave backup configuration for MySQL database under CentOS)

Later reference URL:
http://blog.sina.com.cn/s/blog_6eb46a7e0100wmph.html (CentOS mysql master-slave sync configuration)

http://bbs.csdn.net/topics/380252598 (Error handling)

MySQL error log is usually under the MySQL installation directory under the data directory

The MySQL master-slave synchronization configuration for CentOS