Configuration steps for mysql dual-machine hot standby and mysql dual-machine hot standby

Configuration steps for mysql dual-machine hot standby and mysql dual-machine hot standby

Set dual-machine Hot Standby:

First, you must create a synchronization user on the two machines:

Grant replication slave on *. * to 'repdcs '@ '192. 168.21.39' identified by '123 ';
Grant all privileges on *. * to 'repdcs '@ '192. 168.21.39 identified by '123 ';
Flush privileges;
Grant replication slave on *. * to 'repdcs '@ '192. 168.21.106 'identified by '123 ';
Grant all privileges on *. * to 'repdcs '@ '192. 168.21.106 identified by '123 ';
Flush privileges;

Database 1
[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
User = mysql
Port = 3306
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package ).
Old_passwords = 1
Lower_case_table_names = 1
Default-character-set = utf8
Default-storage-engine = innodb
Max_connect_errorrs = 100000
Innodb_buffer_pool_size = 8G
Max_connections = 500
Default-character-set = utf8
 
Server-id = 2
# Log-bin = mysqlbin
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1
Init_connect = 'set NAMES utf8'
Log-bin = mysqlbin
Master-host = 192.168.21.39
Master-user = repdcs
Master-pass = 123456
Master-connect-retry = 60
Replicate-do-db = dcs
Master-port = 3306
Slave-net-timeout = 60
Database 2
[Mysqld]
# Datadir =/var/lib/mysql
Datadir =/home/data/mysql
# Socket =/var/lib/mysql. sock
Socket =/home/data/mysql. sock
User = mysql
Port = 3306
# Default to using old password format for compatibility with mysql 3.x
# Clients (those using the mysqlclient10 compatibility package ).
Old_passwords = 1
Default-character-set = utf8
Init_connect = 'set NAMES utf8'
# Disabling symbolic-links is recommended to prevent assorted security risks;
# To do so, uncomment this line:
# Symbolic-links = 0
Server-id = 1
Log-bin = mysqlbin
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1
Init_connect = 'set NAMES utf8'
Log-bin = mysqlbin
Master-host = 192.168.21.106
Master-user = repdcs
Master-pass = 123456
Master-connect-retry = 60
Replicate-do-db = dcs
Master-port = 3306
Slave-net-timeout = 60
# Replicate-do-db = dcs
Back_log = 512
Key_buffer_size = 8 M
Max_allowed_packet = 4 M
Sort_buffer_size = 6 M
Read_buffer_size = 4 M
Join_buffer_size = 4 M
Myisam_sort_buffer_size = 64 M
Thread_cache_size = 64
Query_cache_size = 0 M
Tmp_table_size = 96 M
Max_connections = 500
Table_cache = 1024
Innodb_additional_mem_pool_size = 16 M
Innodb_log_buffer_size = 64 M
Read_rnd_buffer_size = 16 M
Innodb_buffer_pool_size = 1G
Innodb_log_file_size = 256 M
Max_heap_table_size = 96 M
Innodb_data_file_path = ibdata1: 200 M: autoextend
Default-storage-engine = innodb
Max_connect_errorrs = 100000
Long_query_time = 1
 
[Mysqld_safe]
Log-error =/var/log/mysqld. log
Pid-file =/var/run/mysqld. pid
 
[Client]
Default-character-set = utf8

If it is marked as red, it mainly involves different parts of the two machines.

Restart if something goes wrong
Show slave status \ G
Last_Error: Error 'can't create database 'dcs '; database exists' on query. Default database: 'dcs '. Query: 'cr
Query the slave database and find that the database to be created already exists. Therefore, you can skip the database creation command.
Use set global SQL _slave_skip_counter = 1;
Start slave SQL _thread;
Flush privileges

Server-id = n // set the database id. The default master server is 1, which can be set at will, but it cannot be repeated if there are multiple slave servers.
Master-host = 192.168.21.39 // ip address or domain name of the master server
Master-port = 3306 // The port number of the master database
Master-user = repdcs // user who synchronizes the database
Master-password = 123456 // password for Database Synchronization
Master-connect-retry = 60 // time difference between the reconnection if the master server is disconnected from the slave server

Report-host = db-slave.mycompany.com // server that reports errors

Then restart the databases of the two machines. The general steps are similar to those of the master and slave databases. For details, refer to mysql master and slave parameter configuration and steps in my blog.

How to Achieve Synchronous Display of Dual-host content

Mysql dual-host hot backup is often used in linux Server lvs load balancing and dual-host hot backup applications. The installation and configuration process is as follows:

1. Install MYSQL

# Cp mysql-standard-4.1.9-pc-linux-gnu-i686.tar.gz/usr/local/

# Tar zxvf mysql-standard-4.1.9-pc-linux-gnu-i686.tar.gz

# Music mysql-standard-4.1.9-pc-linux-gnu-i686 mysql

# Cd mysql

# More INSTALL-BINARY

Check the installation documentation. Note that the installation is mainly in red.

# Groupadd mysql (create a mysql Group)

# Useradd-g mysql (create a mysql user and add the user to this group)

# Scripts/mysql_install_db -- user = mysql (execute the database initialization and installation script as a mysql user)

# Chown-R root. (The root permission is the current directory)

# Chown-R mysql data (the mysql permission belongs to the data directory)

# Chgrp-R mysql. (The user and group owner who changed the mysql file/directory are the current directory)

# Bin/mysqld_safe -- user = mysql & (start the mysql database process in the background as a mysql user)

Then we can start the mysql database,

The figure above indicates that the installation is correct.

To make the database more secure, set the Database Password.

If the mysql database process can be automatically started when the server is started next time, we need to write the/etc/rc. d/rc. local file
Echo "/usr/local/mysql/bin/mysqld_safe -- user = mysql &" >;>;/etc/rc. d/rc. local
　　

Ii. Configure MYSQL dual-machine hot standby

The traditional mode uses mysql dual-host mutual backup. As for dual-host hot backup, I searched a lot of excellent posts and did not find the installation configuration document. So one of my ideas came about: when a new database is created, it will be created in/usr/local/mysql/data by default. If you can change the default configuration file my. conf, the database can be specified in a directory. For example, if I create a database on the disk Cabinet, the dual-machine hot backup function can be implemented.

At first, I was confused about how to change the configuration file my. conf. I consulted a lot of friends, but none of them succeeded. Later, I heard a yddll friend say, "hanging the array File System Under/usr/local/mysql/data", and suddenly opened the door so that I could see Qingtian through the cloud fog. The principles are clear, and we will start to implement them below.

# Fdisk/dev/sdb

# Reboot

# Mkfs. ext3/dev/sdb1 (convert the array format to ext3 format)

# First, we copy all the files in the/usr/local/mysql/data/directory to another directory. I created one here.

# Mkdir-p/bak/mysql_data

# Cd/usr/local/mysql/data/

# Cp-a */bak/mysql_data

# Of ...... the remaining full text>
 
Mysql hot standby

Simple practice: run on the slave machine
Mysql> show slave status \ G;
There are two statuses above. If both are True, the synchronization is normal. In your case, it should not be True.
In the same way, you can see some error messages.
Viewing the mysql error log is also a good choice, generally in the mysql data DIRECTORY

You can try
Mysql> start slave;
This is to enable slave synchronization.