MySQL master and slave library configuration under CentOS 6.4 system

First learn about the one or two
One, (create user under MySQL and give permission) root user Create SQL statement of Yong User
CREATE USER ' yong ' @ ' localhost ' identified by ' yong2022 ';
GRANT USAGE on *. * to ' yong ' @ ' localhost ' identified by ' yong2022 ' with max_queries_per_hour 0 max_connections_per_hour 0 MAX _updates_per_hour 0 max_user_connections 0;

FLUSH privileges;

Second, (Database login)
1. Start the MySQL service in the background
/data/mysql/bin/mysqld_safe&
【
2, the new installation database, set the MySQL user name password
/data/mysql/bin/mysqladmin-u root password ' yong2022 '
】
3. User Login Database
/data/mysql/bin/mysql-u root-pyong2022

Third, MySQL master and slave library configuration

Purpose: The 192.168.73.31 (host) under the root user of the math and mathlog two database backup to the root user of 192.168.73.32 (slave), the other database does not require backup

1, in the host to create user repl, generally do not give the user access to root permissions
CREATE USER ' repl ' @ ' 192.168.73.31 ' identified by ' yong2022 ';
2. Change the host's MySQL configuration file:/data/mysql/etc/my.cnf "# * * * * * * * * * * * * * * * * * * * * * * * *"

[Client]
Port = 3306
Socket =/tmp/mysql.sock

   [mysqld]
   datadir          =/data/mysql/var
   port             = 3306
   socket           =/tmp/mysql.sock
   skip_external_locking
    key_buffer_size = 384M
   max_allowed_packet = 1M
   table_open_cache =
   sort_buffer_size = 2M
   read_buffer_size = 2M
    Read_rnd_buffer_size = 8M
   myisam_sort_buffer_size = 64M
   thread_cache_ Size = 8
   query_cache_size = 32M
   thread_concurrency = 4
    log_bin_trust_function_creators = 1/

# handler Socket
Loose_handlersocket_port = 9998
LOOSE_HANDLERSOCKET_PORT_WR = 9999
Loose_handlersocket_threads = 16
LOOSE_HANDLERSOCKET_THREADS_WR = 1
Open_files_limit = 65535

Log-bin=mysql-bin
Server-id = 1 # * * * *
read-only = 0
BINLOG-DO-DB =math # * * * * *
BINLOG-DO-DB =mathlog # * * * * *
Binlog-ignore-db=information_schema # * * * * does not require backup of database
Binlog-ignore-db=test # * * * * does not require backup of database

# InnoDB
Innodb_data_home_dir =/data/mysql/var
Innodb_data_file_path = Ibdata1:2000m;ibdata2:10m:autoextend
Innodb_log_group_home_dir =/data/mysql/var
Innodb_buffer_pool_size = 384M
Innodb_additional_mem_pool_size = 20M
Innodb_log_file_size = 100M
Innodb_log_buffer_size = 8M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 4M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 256M
Sort_buffer_size = 256M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout

3, log on to the host MySQL root user, execute the following statement to make access to REPL from the library permissions
Mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.73.31 ' identified by ' yong2022 ';
Mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. * to ' repl ' @ ' 192.168.73.31 ' identified by' Yong20 22 ';
Execute statement:
Mysql> Show master status;
At this point, log down the values of file:xxxx and position:yyyy
4. Change the MySQL configuration file from the machine:/data/mysql/etc/my.cnf "# * * * * * * * * * * * * * * * * * * * * * * * * *

   [mysqld]
   datadir          =/data/mysql/var
   port             = 3306
   socket           =/tmp/mysql.sock
   skip_external_locking
    key_buffer_size = 384M
   max_allowed_packet = 1M
   table_open_cache =
   sort_buffer_size = 2M
   read_buffer_size = 2M
    Read_rnd_buffer_size = 8M
   myisam_sort_buffer_size = 64M
   thread_cache_ Size = 8
   query_cache_size = 32M
   thread_concurrency = 4
    log_bin_trust_function_creators = 1/

# handler Socket
Loose_handlersocket_port = 9998
LOOSE_HANDLERSOCKET_PORT_WR = 9999
Loose_handlersocket_threads = 16
LOOSE_HANDLERSOCKET_THREADS_WR = 1
Open_files_limit = 65535

Log-bin=mysql-bin
Server-id = 2 # * * * * differs from host
MASTER-HOST=192.168.73.31 # * * * *
MASTER-USER=REPL # * * * *
MASTER-PASSWORD=YONG2022 # * * * *
master-port=3306 # * * * *
MASTER-CONNECT-RETRY=60 # * * * *
Replicate-do-db=math # * * * *
Replicate-do-db=mathlog # * * * *
Replicate-ignore-db=information_schema # * * * *
Replicate-ignore-db=test # * * * *

# InnoDB
Innodb_data_home_dir =/data/mysql/var
Innodb_data_file_path = Ibdata1:2000m;ibdata2:10m:autoextend
Innodb_log_group_home_dir =/data/mysql/var
Innodb_buffer_pool_size = 384M
Innodb_additional_mem_pool_size = 20M
Innodb_log_file_size = 100M
Innodb_log_buffer_size = 8M
Innodb_flush_log_at_trx_commit = 1
Innodb_lock_wait_timeout = 50

[Mysqldump]
Quick
Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

[Myisamchk]
Key_buffer_size = 256M
Sort_buffer_size = 256M
Read_buffer = 2M
Write_buffer = 2M

[Mysqlhotcopy]
Interactive-timeout

5. Log in from the root user of the machine, execute the following statement
mysql> stop Slave;
mysql> Change Master to master_host= ' 192.168.73.31 ',
Master_user= ' Repl ',

master_password= ' yong2022 ',
master_log_file= ' xxxx ',
master_log_pos=yyyy;
mysql> start slave;
Mysql> Show Slave status\g (This SQL statement adds ";") Will error)
6. When
Slave_io_running:yes
Slave_sql_running:yes
Indicates that the master-slave connection was established successfully.
7, test: Can be in the host's math under the operation, to the root user from the library to verify the view.
This article has been tested.
Reference blog:
http://www.cnblogs.com/wanglikai91/archive/2012/04/17/2454503.html
 http://www.cnblogs.com/yangligogogo/articles/1939938.html
 http://wangwei007.blog.51cto.com/68019/965575

MySQL master and slave library configuration under CentOS 6.4 system