Mysql multi-instance installation and master-slave replication Configuration

Mysql multi-instance installation and master-slave replication Configuration
1. mysql multi-instance installation
1) configuration file
# Cat/etc/my. cnf. multi
[Mysqld_multi]
Mysqld =/usr/bin/mysqld_safe // mysqld path
Mysqladmin =/usr/bin/mysqladmin // mysqladmin path
User = root // user
[Mysqld1] // 1 indicates that the first database instance is identified. The following five variables must be identified under multiple instances:
Port = 3306
Socket =/var/lib/mysql. sock
Datadir =/var/lib/mysql
Pid-file =/var/lib/mysql. pid
User = mysql
[Mysqld2]
Port = 3307
Socket =/var/lib/mysql3307/mysql3307.sock
Datadir =/var/lib/mysql3307
Pid-file =/var/lib/mysql3307/mysql3307.pid
User = mysql
2) configuration process
A. Create datadir =/var/lib/mysql3307
Mkdir-p/var/lib/mysql3307
Chown mysql: mysql/var/lib/mysql3307
B. initialize the 3307 database file
Mysql_install_db-datadir =/var/lib/mysql3307-user = mysql
C. Start Multiple instances
Run mysqld_multi-config-file =/etc/my. cnf. multi report to view the running status of multiple instances. The result is as follows:
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 not running
It indicates that instance 1 is running and instance 2 is not running. You can use:
Mysqld_multi-config-file =/etc/my. cnf. multi start 2 to start instance 2 and query it using the report parameter.
The command to close an instance is:
Mysqld_multi-config-file =/etc/my. cnf. multi stop 2
If no instance number is added after start and stop, all instances are operated! Be careful!
------------------------------------------
2. mysql master-slave replication Configuration
1) Principle of master-slave Replication
Mysql Replication is an asynchronous Replication process, from Master to Slave. The entire replication process in Master and Slave is completed by three threads. Two threads (SQL thread and IO thread) are on the Slave side, and the other thread (IO thread) is on the Master side. To implement the replication process, the Master must enable the Binary Log function. The replication process is the various operations recorded in the execution logs that Slave acquires mysql-bin.xxxxxx logs from the Master and then fully sequential on itself.
2) master-slave replication process
A. the IO thread on the Slave connects to the Master and requests the log content after the specified location of the specified log file (or from the beginning of the log;
B. after the Master receives a request from the Slave IO thread, it reads the log information after the specified log location based on the request information through the IO thread responsible for replication and returns it to the Slave IO thread. Besides the information contained in the Log, the returned information also includes the name of the Binary Log file on the Master end and the position of the Binary Log file in the BinaryLog;
C. after the Slave IO thread receives the information, it writes the received Log content to the end of the Relay Log file at the Slave end in sequence, the file name and location of the bin-log on the Master end are recorded in the master-info file (used in the next Slave request ).
D. after the Slave SQL thread detects the newly added content in the Relay Log, it will immediately parse the content in the Log file into the executable Query statements during actual execution on the Master end, and execute these queries on your own.
-
3) master-slave replication configuration instance
A. master machine: 192.168.100.19 3306, slave machine: 192.168.100.22 3306
B. create a database repl for replication on the master and slave respectively: create database repl;
C. Add the following configuration in [mysqld1] in the configuration file/etc/my. cnf. multi of the master:
Server-id = 1 // master id, usually 1
Log-bin = mysql-bin // enable the Binary Log function
Binlog-do-db = repl // the database to be synchronized. If this parameter is not set, all databases are notified.
D. Add a synchronization account for the slave machine on the master machine.
Mysql> grant replication slave on *. * to 'repl' @ '192. 168.100.22 'identified by 'repl ';
-
E. Add the following configuration in [mysqld1] in the slave configuration file/etc/my. cnf. multi:
Server-id = 2 // slave id
Master-host = 192.168.100.19 // master ip Address
Master-user = repl // account used to connect to the master
Master-password = repl // password used to connect to the master
Master-port = 3306 // master port
Master-connect-retry = 60
Replicate-do-db = repl // database to be synchronized
-
F. Restart the master.
Mysqld_multi-config-file =/etc/my. cnf. multi stop 1
Mysqld_multi-config-file =/etc/my. cnf. multi start 1
Run the show master status Command to view the following information:
Mysql> show master status;
+ ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ---- + ----- + ------ +
| Log.000002 | 626 | repl |
+ ---- + ----- + ------ +
G. Restart slave
Mysqld_multi-config-file =/etc/my. cnf. multi stop 1
Mysqld_multi-config-file =/etc/my. cnf. multi start 1
Use show slave status to view the following information: (if the Slave_IO_Running and Slave_ SQL _Running statuses are Yes, the setting is successful)
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.19
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log.20.02
Read_Master_Log_Pos: 626
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 229
Relay_Master_Log_File: log.20.02
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: repl
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: 626
Relay_Log_Space: 229
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: 0
1 row in set (0.00 sec)
-
H. Test
Create a table on the master and insert data:
Mysql> create table 'vendor '(
-> 'Vend _ id' int (11) not null auto_increment,
-> 'Vend _ name' char (50) not null,
-> 'Vend _ address' char (50) default NULL,
-> 'Vend _ City' char (50) default NULL,
-> 'Vend _ state' char (5) default NULL,
-> 'Vend _ zip' char (10) default NULL,
-> 'Vend _ country' char (50) default NULL,
-> Primary key ('vend _ id ')
-> );
Mysql> insert into vendors values ('1', 'zou', 'tianhe ', 'guangzhou', 'cn', '000000', 'zhongguo ');
Check whether data exists in slave:
Mysql> select * from vendors;
+ --- + ---- + ----- +
| Vend_id | vend_name | vend_address | vend_city | vend_state | vend_zip | vend_country |
+ --- + ---- + ----- +
| 1 | zou | tianhe | guangzhou | cn | 5100 | zhongguo |
+ --- + ---- + ----- +
------------------------------------------
3. Other common parameters for mysql Configuration
Skip-locking // avoid mysql external locks, reduce the chance of errors, and enhance stability
Skip-name-resolve // cancel reverse DNS resolution
Back_log = 64 // specify the number of possible MySQL connections. This parameter specifies the parameter value that specifies the number of requests that can be stored in the stack within a short period of time before MySQL temporarily stops responding to a new request.
Key_buffer_size = 256 M // the buffer size used for index blocks. Increase the buffer size to produce better processed indexes.
Max_allowed_packet = 8 M // maximum size of a query statement package.
Table_cache = 256 K // number of tables opened by all threads
Sort_buffer_size = 6 M
Net_buffer_length = 256 K Communication buffer is reset to this size during Query
Read_buffer_size = 4 M // the buffer size that can be used by the read query operation. If there are 100 connections, the actually allocated buffer is 100*4 = 400 M.
Read_rnd_buffer_size = 2 M
Join_buffer_size = 8 M // the buffer size used for all join operations (not the join operation using the index ). The buffer zone allocates a buffer for each join between two tables. When an index is increased, increasing this value will allow you to get a faster join. (The best way to get a quick join is to increase the index .)
Myisam_sort_buffer_size = 64 M
Query_cache_size = 8 M
Max_connections = 2000 // number of customers allowed to connect to the MySQL server at the same time. If this value is exceeded, MySQL returns the Too connector connections error.
Max_connect_errors = 99999 // if there are more than this number of interrupted connections, further connections will be blocked. If you need to unban the HOST, execute: flush host;
Thread _ cache_size = 128
Thread_concurrency = 8
Log_slow_queries = slow_queries.log // enable slow query log
Long_query_time = 1 // if the time used by a query is later than this time, the slow_queried count will increase
Default-character-set = utf8
Lower_case_table_names = 1 // MySQL always uses lowercase letters for SQL operations
Innodb_buffer_pool_size = 3000 M // maximum memory buffer size of the table data and index data of the InnoDB Storage engine. Increase the size of this parameter as appropriate, this effectively reduces the disk I/O of InnoDB tables. On an InnoDB-based dedicated database server, you can set this parameter to 60%-80% of the physical memory size.
Innodb_additional_mem_pool_size = 50 M // this parameter is used to set the data directory information stored in InnoDB and the memory pool size of other internal data structures. If InnoDB uses up the memory in this pool, InnoDB allocates memory from the operating system and writes warning information to the MySQL error log.
Expire_logs_days = 30 // Number of days when Binary Log is automatically deleted

 

This article is from "keyboard goes out of the rivers and lakes"