MySQL master-Slave Configuration summary

MySQL master-Slave Configuration summary

1.[[email protected] ~]# cat/etc/redhat-release//version centos6.4
CentOS Release 6.4 (Final)

2.[[email protected] ~]# Virsh
Welcome to Virsh, the virtualization interactive terminal.

Type: ' Help ' for help with commands
' Quit ' to quit

3.virsh # List
Id Name State
----------------------------------------------------
1 windows2003_64 Running
2 WinXP Running
4 CactiEZ Running
SN01 running//KVM virtual machine SN01 for centos6.4 os as main MySQL ip:192.168.200.77
SN02 running//KVM virtual machine SN01 for centos6.4 operating system as from MySQL ip:192.168.200.78

4.virsh # Console 11//Enter the main MySQL server

5.[[email protected] mysql]# rpm-q mysql-server//mysql version 5.1.73 version
Mysql-server-5.1.73-3.el6_5.x86_64

6.[[email protected] mysql]# cat/etc/my.cnf//Add Server-id and Log-bin=mysql-bin
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql

server-id=77//Add Server-id cannot repeat this for IP last one as ID
Log-bin=mysql-bin//Start bin Log

7.[[email protected] mysql]# mysql-uroot-p123456//Enter MySQL database
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 28
Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

8.mysql>grant replication Slave on * * to ' replication ' @ ' 192.168.200.% ' identified by ' 123456 ';//do not normally use root account, "%" Indicates that all clients may connect, as long as the account number, the password is correct, here can be specific

Client IP instead, such as 192.168.200.78, strengthens security.

Query OK, 0 rows Affected (0.00 sec)

9.mysql> Show master status; View the status of master MySQL remember file and position
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000001 |              187 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Do not operate the master server MySQL after performing this step to prevent the change of the primary server state value

10. Press CTRL +] to return to KVM

11.virsh # List
Id Name State
----------------------------------------------------
1 windows2003_64 Running
2 WinXP Running
4 CactiEZ Running
SN01 Running
SN02 Running

12.virsh # Console 13//Enter SN02 from MySQL server
Connected to Domain SN02
Escape character is ^]

13.[[email protected] mysql]# mysql-uroot-p123456//Enter from MySQL database server
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 10
Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

14.mysql> Change Master to master_host= ' 192.168.200.77 ', master_user= ' replication ', master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000001 ', master_log_pos=187;//note do not disconnect, "187" without single quotation marks.

15.mysql> start slave; Start the server replication feature
Query OK, 0 rows affected, 1 Warning (0.00 sec)

16.mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.200.77//Primary server address
Master_user:replication//Authorization account name, try to avoid using root
master_port:3306//Database port, some versions do not have this line
Connect_retry:60
master_log_file:mysql-bin.000001
read_master_log_pos:187//#同步读取二进制日志的位置, greater than or equal to >=exec_master_log_pos
relay_log_file:mysqld-relay-bin.000022
relay_log_pos:251
relay_master_log_file:mysql-bin.000001
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:187
relay_log_space:552
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:
1 row in Set (0.00 sec)

ERROR:
No query specified

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).

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

Test

17.mysql> show databases; View all databases
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
| |
+--------------------+
3 Rows in Set (0.00 sec)

18.mysql>create database zhh; Create a database zhh

19.mysql> use zhh;//into ZHH database
Database changed

20.mysql> CREATE TABLE zhh (ID int (3), name char (10));//creating ZHH Table
Query OK, 0 rows affected (0.06 sec)

21.mysql> INSERT into Zhh value (, ' zh888.blog ');//Insert 01 and Zh888.blog values
Query OK, 1 row affected, 1 Warning (0.00 sec)

22.mysql> Select *from zhh;//Query the contents of the ZHH table
+------+------------+
| ID | name |
+------+------------+
| 1 | Zh888.blog |
+------+------------+
1 row in Set (0.00 sec)

23.ctrl+] Switch to KVM

24.virsh # List
Id Name State
----------------------------------------------------
1 windows2003_64 Running
2 WinXP Running
4 CactiEZ Running
SN01 Running
SN02 Running

25.virsh # Console 13//Connect sn02 from MySQL server
Connected to Domain SN02
Escape character is ^]

26.[[email protected] mysql]# mysql-uroot-p123456//Enter from MySQL database

Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 12
Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

27.mysql> show databases;//View all MySQL databases

+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
| zhh |
| |
+--------------------+
4 rows in Set (0.01 sec)

28.mysql> use zhh;//into ZHH database
Database changed

Mysql> Select *from zhh;//Query the contents of the ZHH table
+------+------------+
| ID | name |
+------+------------+
| 1 | Zh888.blog |
+------+------------+
1 row in Set (0.00 sec)

Done!

This article is from the "a Willing heart!" blog, be sure to keep this source http://zh888.blog.51cto.com/1684752/1600087

MySQL master-Slave Configuration summary