MySQL master-slave configuration (based on centos6.5)

MySQL master-slave configuration (based on centos6.5)

Master

Install MySQL

Yum-y Install Mysql-srver

Start MySQL

Service mysqld Start

Log in to MySQL and modify the password and delete the empty user

Mysql-u Root

mysql> UPDATE mysql.user SET password = password (' 123456 ') WHERE user = ' root ';

Query OK, 3 Rows Affected (0.00 sec)

Rows Matched:3 Changed:3 warnings:0

Mysql> SELECT User,host,password from Mysql.user;

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

| user | Host | password |

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

| Root | localhost | *7ae39be5035d5c32361400ff7dedd757aa76896a |

| Root | 10-4-14-168 | *7ae39be5035d5c32361400ff7dedd757aa76896a |

| Root | 127.0.0.1 | *7ae39be5035d5c32361400ff7dedd757aa76896a |

| |                                           localhost | |

| |                                           10-4-14-168 | |

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

5 rows in Set (0.00 sec)

mysql> DROP user ' @localhost;

Query OK, 0 rows Affected (0.00 sec)

mysql> DROP user ' @ ' 10-4-14-168 ';

Query OK, 0 rows Affected (0.00 sec)

Master needs to change the configuration file and add it to the/etc/my.cnf.

[Mysqld]

Port = 3306

Log_bin =/var/lib/mysql/mysql-binlog

Server-id = 1

binlog_do_db = Test

Create a replication user on Master

GRANT REPLICATION slave,replication CLIENT on * * to [e-mail protected] '% ' identified by ' Tongbu ';

Slvae:

Install MySQL

Yum-y Install Mysql-srver

Start MySQL

Service mysqld Start

Log in to MySQL and modify the password and delete the empty user

Mysql-u Root

mysql> UPDATE mysql.user SET password = password (' 123456 ') WHERE user = ' root ';

Query OK, 3 Rows Affected (0.00 sec)

Rows Matched:3 Changed:3 warnings:0

Mysql> SELECT User,host,password from Mysql.user;

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

| user | Host | password |

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

| Root | localhost | *7ae39be5035d5c32361400ff7dedd757aa76896a |

| Root | 10-4-14-168 | *7ae39be5035d5c32361400ff7dedd757aa76896a |

| Root | 127.0.0.1 | *7ae39be5035d5c32361400ff7dedd757aa76896a |

| |                                           localhost | |

| |                                           10-4-14-168 | |

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

5 rows in Set (0.00 sec)

mysql> DROP user ' @localhost;

Query OK, 0 rows Affected (0.00 sec)

mysql> DROP user ' @ ' 10-4-14-168 ';

Query OK, 0 rows Affected (0.00 sec)

Slave need to change configuration file, add in/ETC/MY.CNF

[Mysqld]

Server-id = 2

Master-host = 192.168.10.122

Master-user = Tongbu

Master-password = Tongbu

Master-port = 3306

Master-connect-retry = 5

REPLICATE-DO-DB = Test

Restart the service separately and log in to the database.

Master

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      binlog.000003 |              412 |                  | |

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

1 row in Set (0.00 sec)

Slave

Mysql> Show Slave Status\g

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

Slave_io_state:waiting for Master to send event

master_host:10.4.14.168

Master_user:gechong

master_port:3306

Connect_retry:5

master_log_file:mysql-binlog.000001

read_master_log_pos:325

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:473

relay_master_log_file:mysql-binlog.000001

Slave_io_running:yes

Slave_sql_running:yes

Replicate_do_db:test

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

relay_log_space:629

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)

MySQL master-slave configuration (based on centos6.5)