Attacking Mysql-Slave environment construction and Configuration

1) Environment Topology

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2232221632-0.png "title =" QQ20130614135543.png "/>

Today we are talking about the mysql cluster. The resources are as follows:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/223222O20-1.png "title =" QQ20130614140136.png "/>

Ii) install and configure Mysql

Here we will not go into details here. You can choose to compile and install the source code, or refer to my previous blog post "lazy" to speed up the linux LAMP environment.

3) node configuration

Master node:

1.First, create two databases and tables in the database:

#service mysqld start#mysqlmysql>create database www;mysql>use www;mysql>create table www(id int);mysql>insert into www values(1);mysql> select * from www;

View data: 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22322252K-2.png "title =" 111.png"/>;

Similarly, create a blog database and table:

mysql>create database blog;mysql>use blog;mysql>create table blog(id int);mysql>insert into blog values(1);mysql> select * from blog;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2232223049-3.png "title =" 1.png"/>

2. Modify the My. cnf configuration file

vi etc/my.cnf

[Mysqld] datadir =/var/lib/mysqlsocket =/var/lib/mysql. sockuser = mysql # Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links = 0log-bin = mysql-bin // logs are in binary format, you do not need to change server-id = 1 // to 1 as the Master, binlog-do-db = blog // binlog-do-db = www // database to be synchronized binlog-ignore-db = mysql, test, information_schema // The Name Of The database that does not need to record logs. Multiple databases are separated by commas (,) innodb_data_home_dir =/usr/local/mysql/data // innodb tablespace location innodb_data_file_path = ibdata1: 50 M: autoextend // tablespace name, start logging =/usr/local/mysql/data/innodb_buffer_pool_size = 256 M // 50-80% bytes = 1innodb_lock_wait_timeout = 50 [mysqld_safe] log -error =/var/log/mysqld. logpid-file =/var/run/mysqld. pid

3. Restart the database

[root@Test01 /]# service mysqld restart

Stopping mysqld:  [  OK  ]Starting mysqld:  [  OK  ]

4. Create an account with permissions to allow the Slave database to access master dataLibrary

[root@Test01 /]# mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.1.69-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

mysql> grant replication slave on *.* to repl@"192.168.1.26" identified by '123456';Query OK, 0 rows affected (0.00 sec)

mysql>flush privileges;;

Note: Format: grant replication slave on *. * TO 'account' @ 'slave Server IP address or hostname 'identified BY 'Password ';

5. Back up the Master database

Mysql> flush tables with read lock; // do not exit the terminal; otherwise, the lock will not take effect. Take snapshots at the same time, and record the log and offset: mysql> show master status; + ------------------ + ---------- + -------------- + Position + | File | Position | Binlog_Do_DB | bytes | + -------------------- + ---------- + ------------ + others + | mysql-bin.000001 | 196 | blog, www | mysql, test, information_schema | + ------------------ + ---------- + ---------------- + ----------------------------- + 1 row in set (0.00 sec)

6. Enable another terminal to take snapshots of the master database.

[root@Test01 mysql]#cd /var/lib/mysql/[root@Test01 mysql]#tar -zcvf backup.tar.gz www blog

7. Database unlocking

mysql> unlock tables;

Slave node:

1. Install mysql in the same way as above.

2. Modify the My. cnf configuration file

[Root @ Test02 mysql] # vi/etc/my. cnf [mysqld] datadir =/var/lib/mysqlsocket =/var/lib/mysql. sockuser = mysql # Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links = 0log-bin = mysql-binserver-id = 2 # master-host = 192.168.1.10 # master-user = root # master-password = 584911644 # master-port = 3306 # master-log-pos = 196 # master-log-file = mysql-bin.000001 # master-connect-retry = 60replicate-do-db = www // tell slave only updates www database replicate-do-db = blog // tell slave only updates log-slave-updates [mysqld_safe] log-error =/var/log/mysqld. logpid-file =/var/run/mysqld. pid "/etc/my. cnf "23L, 551C

3. Copy the compressed data from the master database to the corresponding location.

I am using SecureFX here. Of course, there are many methods and I will not elaborate on them. It can be said that a great road passes Rome.

4. Start the database

[root@Test02 mysql]# service mysqld start

Starting mysqld:  [  OK  ]

5. Configure and start Slave

Note: Slave_IO_Running: whether to repeat Binary Log data from the Master Server, which must be Yes. Slave_ SQL _Running: whether to execute Binary Log data from the Master Server, which must be Yes.

mysql> slave stop;Query OK, 0 rows affected (0.00 sec)mysql> change master to master_host='192.168.1.10',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=196;Query OK, 0 rows affected (0.04 sec)mysql> slave start;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.10                  Master_User: repl                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 339               Relay_Log_File: mysqld-relay-bin.000002                Relay_Log_Pos: 394        Relay_Master_Log_File: mysql-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB: www,blog          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: 339              Relay_Log_Space: 550              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: 0Master_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)

4) post-test

Master node-data increase:

mysql>  insert into www values(2);Query OK, 1 row affected (0.00 sec)mysql> select * from www;+------+| id   |+------+|    1 ||    2 |+------+2 rows in set (0.00 sec)

Slave node data synchronization Verification:

mysql> select * from www;+------+| id   |+------+|    1 ||    2 |+------+2 rows in set (0.00 sec)

Master node-data deletion:

mysql> delete from www  where id ='2';Query OK, 1 row affected (0.04 sec)mysql> select * from www;+------+| id   |+------+|    1 |+------+1 row in set (0.00 sec)

Slave node data synchronization Verification:

mysql> select * from www;+------+| id   |+------+|    1 |+------+1 row in set (0.00 sec)

Summary:

Test02: Synchronize updates to the database from the database. Currently, there are two tables: WWW and blog. Of course, this is just an example. You can set up and configure the best environment based on your actual needs.

Recently, we have been working on RHCS and keepalived. When we are free, we will record several database slave configurations, so that you can learn and accumulate more.

This article is from the "Aaron" blog, please be sure to keep this source http://qishiding.blog.51cto.com/3381613/1221960