MySQL dual-Machine hot standby deployment

Uninstall the version that comes with your system.

This is a version of Mysql-5.5.28-1.

For a simple, RPM-style installation:

RPM-IVH mysql-server-5.5.28-1.linux2.6.x86_64.rpm

RPM-IVH mysql-client-5.5.28-1.linux2.6.x86_64.rpm

#mysql

Mysql>update Mysql.user Set password= "Chengshan" where user= "root"; //

Mysql>delete from Mysql.user where user= '; //Delete anonymous user

Mysql>select user,host from Mysql.user;

Mysql>delete from Mysql.user where host= ' 127.0.0.1 ';

Mysql>delete from mysql.user where host= ' localhost ';

Mysql>create user ' Zcs ' @ ' 127.0.0.1 ' identified by ' Chengshan ';

Mysql>grant all privileges on * * to ' root ' @ '% ' identified by ' Chengshan ' with GRANT option;

Mysql>grant all privileges on * * to ' zcs ' @ '% ' identified by ' Chengshan ' with GRANT option;

Mysql>flush privileges;

Mysql>quit

Login Test

[Email protected] ~]# mysql-u root-p

Enter Password:

1. Modify the relevant configuration file:

#cp/usr/share/mysql/my-medium.cnf/etc/my.cnf

#vi/etc/my.cnf

Last added in [client]

Default-character-set=utf8

Added at last [mysqld]

Lower_case_table_names=1

transaction-isolation=read-committed

Event-scheduler=1

max_connections=2000

2. Start

/etc/init.d/mysql start

#netstat-ANLT

3. Stop

/etc/init.d/mysql stop

4. Restart

/etc/init.d/mysql restart

5. Set auto-start

/sbin/chkconfig MySQL on

22 servers are installed, the dual-machine hot standby configuration (master-slave relationship)

Contract Environment: The host name of the two servers is best different, such as one is DB1 (master), one is DB2 (from).

Can be implemented by modifying/etc/sysconfig/network

The root password for both databases is Chengshan

1 Entering the primary server

Mysql–u root–p

Password: Chengshan

Mysql> input: Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|     mysql-bin.000002 |              107 |                  | |

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

1 row in Set (0.00 sec)

If the prompt is empty, you need to add the mysqld in the/ETC/MY.CNF

Log-bin=mysql-bin

server-id=10

Note the parameters inside, especially the first two file and position, which can be useful to configure the master-slave relationship from the server (Slave).

Create a synced user

Grant Replication Slave on * * to [e-mail protected] identified by ' 123456 ';

Create a user user1 password of 123456, which specifies that the IP is accessible from the server.

Flush privileges;

Modify/ETC/MY.CNF

Modify under [mysqld]:

[Mysqld]

Server-id = 1

Log-bin=mysql-bin//One of these two lines is the original, you can not move, add the following two lines can be

BINLOG-DO-DB = OPTS2//Specifies the name of the database that needs to be synchronized, such as Opts2

binlog-ignore-db = mysql//Specifies that the database name that does not need to be synchronized, such as MySQL's own MySQL or other database, if you do not specify that all of the two defaults are synchronized.

After you've modified it.

Service MySQL restart//restart MySQL service, not explained. If it succeeds, it's fine.

The master server configuration is complete.

2 Entering from the server

First modify the/ETC/MY.CNF configuration

[MYSQLD]//modified after [MYSQLD] paragraph

Server-id = 2//modify 1 to 2

Log-bin=mysql-bin//default configuration, turn on logging, do not change

REPLICATE-DO-DB = OPTS2//Specifies the name of the database to which the primary server initiates synchronization, such as Opts2

Replicate-ignore-db = Mysql,information_schema,performance_schema

Perform database names that do not need to be synchronized

Service MySQL restart//not explained

Specifying a synchronization location with the change Mster statement

Mysql–u root–p

Password: Chengshan

Mysql>

Create a synced user

Grant Replication Slave on * * to [e-mail protected] identified by ' 123456 ';

Create a user user2 password of 123456, which specifies that IP is accessible to the primary server. In fact, this sentence can not play, this sentence is to each other back up with the (main-main relationship), but with nothing, not set permissions.

Mysql>stop slave; It is important to stop the slave service thread first, and if you do not do this it will cause the following operations to be unsuccessful.

Mysql>change Master to

->master_host= ' 192.168.0.90 ',

->master_user= ' User1 ',

->master_password= ' 123456 ',

->master_log_file= ' mysql-bin.000003 ',

->master_log_pos=107;

Note: Master_log_file, Master_log_pos is determined by the status value identified by the master server (master). It's just called attention. Master_log_file corresponds to file, Master_log_pos corresponds to position.

Mysqlbinlog mysql-bin.000003 > 0.txt

After the carriage return, if the success will have the OK prompt, if is unsuccessful, must reset the slave. Instructions are as follows

Mysql>stop slave;

Mysql>reset slave;

Then stop the slave thread to start again. After success, you can turn on the slave thread.

Flush privileges;

Mysql>start slave; Restart

Mysql>show slave status\g; View from server Status

# service MySQL restart//not explained

This is done from the server Setup.

MySQL dual-Machine hot standby deployment