mysql5.1.73 Configuring the master-Slave server

First, install MySQL

Here is a detailed explanation, please degree Niang.

Second, configure MySQL master server (10.241.226.110)

Mysql-uroot-p #进入MySQL控制台
Create DATABASE TestDB; #建立数据库testdb

#授权用户mysqlcopy只能从 10.241.226.111 this IP access master server 10.241.226.110 above the database and only has database backup permissions
grant replication Slave  on * * to ' mysqlcopy ' @ ' 10.241.226.111 ' identified by ' 123456 ' with Grant option; 

three, the MySQL master server 10.241.226.110 in the database testdb import to MySQL from server 10.241.226.111 in
1, export database testdb

Mysqldump-u root-p testdb>/home/testdbbak.sql

Note: Before exporting, you can go to the MySQL console and execute the following command

Flush tables with read lock; #数据库只读锁定命令 to prevent data from being written when the database is exported

Unlock tables; #解除锁定

2. Import database to MySQL from server

Mysql-u root-p #进入从服务器MySQL控制台

Create DATABASE TestDB; #创建数据库

Use testdb #进入数据库

source/home/testdbbak.sql #导入备份文件到数据库

Mysql-u mysqlcopy-H10.241.226.110 -P #测试在从服务器上登录到主服务器

Iv. Configuring the MySQL master server my.cnf file

1, Vim/etc/my.cnf #编辑配置文件, in the [Mysqld] section add the following content

Log_bin=mysql-bin  #启动MySQ二进制日志系统, note: If you already have this line in your original configuration file, you won't have to add it anymore. Binlog-do-db=testdb  #需要同步的数据库名, if you have multiple databases, repeat this parameter, one row per database binlog-ignore-db=mysql   #不同步mysql系统数据库server_id = 1  #设置服务器id, represents the primary server for 1, note: If you already have this line in the original configuration file, you don't have to add it anymore. Socket =/var/lib/mysql/mysql.sockcharacter-set-server=utf8

2, service mysqld  restart   #重启MySQL
3, mysql-u root- p    #进入mysql控制台
4, show Master status;   to view the primary server, the following similar information appears
+------- -----------+----------+--------------+------------------+
| file                         | Position  | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    120    | testdb    | mysql                   |
+------------------+----------+--------------+------------------+

Note: The value of file is remembered here: the value of mysql-bin.000001 and position: 120, which is used later.

V. Configure MySQL MY.CNF files from the server

1, Vi/etc/my.cnf #编辑配置文件, in the [Mysqld] section add the following content

Log_bin=mysql-bin   #启动MySQ二进制日志系统, note: If you already have this line in your original configuration file, you won't have to add it anymore. replicate-do-db=testdbreplicate-ignore-db=mysqlread_only=1server_id = 2   

2, service mysqld restart #重启MySQL
3, Mysql-u root-p #进入MySQL控制台
4, slave stop; #停止slave同步进程
5, change master to master_host= '10.241.226.110', master_user= ' mysqlcopy ', master_password= ' 123456 ', master_    Log_file= ' mysql-bin.000001,master_log_pos=120; #执行同步语句
6, slave start; #开启slave同步进程

7, SHOW SLAVE status\g #查看slave同步信息, part of the following:

1. Row *************************** slave_io_state:waiting for master to send event Master_hos  t:10.241.226.111 master_user:mysqlcopy master_port:3306 connect_retry: master_log_file:mysql-bin.000001 read_master_log_pos:377 Relay_log_file:localho st-relay-bin.000003 relay_log_pos:540 relay_master_log_file:mysql-bin.000001 Slave_io_ Running:yes Slave_sql_running:yes Replicate_do_db:db_cloudcore Replicate_ignore_db:my SQL Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Ta ble:last_errno:0 last_error:skip_counter:0 Exec_master_ log_pos:377 relay_log_space:717 until_condition:none Until_log_file:

Note check:
Slave_io_running:yes
Slave_sql_running:yes
The values for these two parameters are yes, which means the configuration was successful!

Six, test MySQL master server dual-Machine hot standby is successful
1. Go to MySQL master server

Mysql-u root-p #进入主服务器MySQL控制台

Use TestDB; #进入数据库

CREATE TABLE Test (id int not NULL); #创建test表
2. Enter MySQL from the server

Mysql-u root-p #进入MySQL控制台

Use TestDB; #进入数据库

Show tables;

You see a new table test that indicates that the database synchronization was successful

Problem:

1. ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/tmp/mysql.sock '

A: Because MySQL default mysql.sock is in/var/lib/mysql/mysql.sock, but the Linux system always go to/tmp/mysql.sock find, so will error

Add a soft connection (equivalent to a shortcut in Windows) for Mysql.sock.
Ln-s/var/lib/mysql/mysql.sock/tmp/mysql.sock

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

mysql5.1.73 Configuring the master-Slave server