Cenos 6.3 under mysql-5.5.25 master-Slave server configuration

Environment Preparation:

Install Cenos6.3 on the virtual machine, and then download and install MySQL,

about installing mysql-5.5.25 under CenOS6.3 here is not the repetition, please look at the previous blog http://shansongxian.blog.51cto.com/5040181/1429804, because we are the MySQL master server, Must have two virtual machines to test, if it is too cumbersome to install again, here we use the cloning of VMware virtual machine, the installation of the MySQL server shut down, on all the tabs right-click on the----management----clone----cloned from " The current state in the virtual machine----Create a linked clone and select the Clone save path, all of which do not select full cloning because the time is too long

Start the cloned virtual machine, here we also need to solve a problem, the cloned virtual machine NIC device does not recognize the boot

WORKAROUND: 1. Restart the server after removing/etc/udev/rules.d/70-persistent-net.rules

70-persistent-net.rules This file determines the binding of the NIC to the MAC address

2. After restarting the server, use VI to open/etc/udev/rules.d/70-persistent-net.rules This file, remember the MAC address, open the network configuration file, change hwaddr= to just the MAC address to restart the server again, The NIC is ready to use

MySQL master server ip=192.168.100.230 hostname=master.abc.com

MySQL from server ip=192.168.100.231 hostname=slave.abc.com

1. Configuration of the primary server

Vim/etc/my.cnf

[Mysqld]

Log-bin=mysql-bin//[must] enable binary logging

server-id=230//[must be] server unique ID, default is 1, usually take IP last paragraph

The MySQL service needs to be restarted after modifying the configuration file.

Service mysqld Restart

2. Create an account on the master server and authorize

Log in to the MySQL server using the root user

Mysql>grant REPLICATION SLAVE on * * to ' huqiang ' @ ' percent ' identified by ' HDZZJSJ ';

Generally do not use the root account, "%" means that all clients may be connected, as long as the account, the password is correct, here can be specific client IP instead, such as 192.168.100.231, enhance security.

Mysql>flush privileges; Refresh System Permissions Table

3. Querying master server Master status

mysql> mysql>show Master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000006 |              328 |                  | |

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

1 row in Set (0.00 sec)

At this point, do not move the master server, to prevent changes in status values

4. Configuration from the server

Vim/etc/my.cnf

[Mysqld]

Log-bin=mysql-bin//[must] enable binary logging

server-id=231//[must be] server unique ID, default is 1, usually take IP last paragraph

The MySQL service needs to be restarted after modifying the configuration file.

Service mysqld Restart

5. Use the root user to enter the slave server

mysql> Change Master to master_host= ' 192.168.100.230 ', master_user= ' Huqiang ', mync ', master_password= ' HDZZJSJ ', Master_log_file= ' mysql-bin.000006 ', master_log_pos=328;

Note that the to is followed by a space do not break, "328" without single quotation marks.

mysql> start slave; To start the Copy from Server feature

6. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:192.168.100.230//Primary server address

Master_user:huqiang//Authorized user, try not to root user

master_port:3306//Port

Connect_retry:60

master_log_file:mysql-bin.000006

read_master_log_pos:328

relay_log_file:www-relay-bin.000002

relay_log_pos:253

relay_master_log_file:mysql-bin.000006

Slave_io_running:yes// This status must be Yes

Slave_sql_running:yes// This status must be 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:328

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:

Replicate_ignore_server_ids:

master_server_id:230

1 row in Set (0.00 sec)

The slave_io and Slave_sql processes must be running normally, that is, the Yes state, otherwise it is the wrong state (e.g., one of the No is an error).

The above operation process, the master and slave server configuration is complete.

7.

Master-Slave Server testing:

master server MySQL, build the database, and create a table in this library to insert a piece of data:

Mysql> CREATE DATABASE Jack;

Mysql> use Jack;

Mysql> CREATE TABLE ABC (

-ID int primary KEY auto_increment,

Sname varchar (+) NOT null default ' ',

-Sex char (1) NOT null default ' '

) engine MyISAM charset UTF8;

Mysql> INSERT INTO ABC (SNAME,SEX) VALUES (' Zhang San ', ' Male ');

Mysql> SELECT * FROM ABC;

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

| ID | sname | sex |

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

| 1 | Zhang San | Male |

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

1 row in Set (0.00 sec)

Log in from MySQL server, view, OK

If the data from the primary server is not synchronized from the server and you view the status from the server, you find that there is an error message:

"Last_io_error:got fatal Error 1236 from master if reading data from binary log: ' Could not ' find first log file name in Binary Log index file ' "

This is due to the master-slave server data is not synchronized, this can be handled by the following steps:

Stop slave from the server by using the command from the server.

Use the command flush logs on the primary server to refresh the primary server log information.

Use the command show Master status on the primary server, view the primary server state, and focus primarily on the log file name and location offset (position).

Execute command change from server to master_log_file= ' mysql-bin.00004 ', master_log_pos=397; note the file name and position are corresponding on the primary server.

Use the command start slave; start the slave thread from the server.

Once again, insert the data in the Primary server table and you should be able to synchronize normally.

This article from "Unchanged Time" blog, declined reprint!

Cenos 6.3 under mysql-5.5.25 master-Slave server configuration