MySQL partitioning and master-slave learning Notes

1 Zoning related

In MySQL5.6, partitioning technology only supports horizontal partitioning, not vertical partitioning, and partitioning supports most storage engines, but the merge, CSV, Federated storage engines are not supported.

Partitioning using key or linear key is supported for NDB (network databases), but other partitioning methods are not supported.
In MySQL to see if it is support zoning technology, using the command show PLUGINS to view, if there is a plug-in name partition, the status of active is the use of partitioning technology.
MySQL5.6 supports explicit partitioned queries, such as SELECT * from T PARTITION (P0, p1) WHERE C < 5, which can be queried only from the P0,P1 partition

Types of partitions supported
:
Range is partitioned based on the range of values for a given column.
A list is similar to a range, but it determines whether the value of a given column is partitioned in a collection of independent values.
HASH This partition is partitioned based on the value of the custom expression that operates on the column. This custom function can contain any available MySQL expressions, but it needs to produce a non-negative value with an extended type of linear HASH.
KEY This partitioning method is similar to the hash, the difference is that only need to provide a column or a number of columns of values, through MySQL's own hash function to calculate the extension type has linear KEY.
The common partitioning method can be partitioned by date and time. When partitioning using key or Linear key, you can use time, DATETIME, date as the partitioning column without any modification required (commonly used: To_days (), year, To_seconds, weekday () , month, etc.).

2 principal-Subordinate correlation

Both the master and the library need to be configured with a unique ID (server-id), each of which must configure the host name of the master library, the log file name, and the location in the configuration file from the library.

2.1 Configuration Master

To modify MY.CNF or My.ini files, in the [Mysqld] section, add (remove comments) The following options:
[Mysqld]
Log-bin=mysql-bin
Server-id=1

Restart the MySQL service after modification
If Server-id is not set or if it is set to the default value 0,master will reject all slave connection requests. Transactions that use InnoDB should be set to innodb_flush_log_at_trx_commit=1 and sync_binlog=1 for optimal stability when replicating. Make sure the skip-networking option is prohibited, otherwise the library will not be able to communicate with the main library if the network is disabled.
The main library needs to create a connection user from the library, and any user with replication slave permissions on the main library can serve as a connection user from the library.
mysql> CREATE USER ' repl ' @ '%.mydomain.com ' identified by ' slavepass ';
Mysql> GRANT REPLICATION SLAVE on *.* to ' repl ' @ '%.mydomain.com ';
If you already have data on the main library, you must stop the processing process on the main library before synchronizing with the library, and then get the coordinates of the current binary log file and dump its data.
The following steps are used to get the coordinates of the binary log of the main library:
Using the command line console to connect to the main library, execute the following command:

Mysql> FLUSH TABLES with READ LOCK;
Use UNLOCK TABLES to release locks
 mysql > show MASTER STATUS;
 +------------------+----------+------------ --+------------------+
 | file             | Position | binlog_do_db | binlog_ignore_db |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000003 | 73        | test         | manual,mysql     |
 +------------------+----------+--------------+------------------+
 
If before Log-bin is enabled, The main library has been running for some time, the log file name and location that is checked with the show Master Status command will be empty, in which case the log file and location are specified as ' and 4 ' from the library.
If the data already exists in the main library before synchronizing replication from the library, keep the read lock, and use the following method to copy the data from the main library to the library.
Use mysqldump to create a snapshot of all the databases that you want to replicate, and then import them into the library.
# mysqldump-uroot--lock-all-tables--events--all-databases--master-data > Dbdump.db
Import dbdump.db file, use command shell> MySQL < dbdump.db

2.2 Configuration Slave

Modify configuration file
[Mysqld]
server-id=2
From the library, you do not need to enable log-bin for the replication process. However, if you enable Log-bin from the library, you can perform binary log backups and crash recovery from the library, or as part of a complex replication topology network from the library (for example, from the library as a master library from the library).
To establish a connection from the library to the main library:
Mysql> Change MASTER to
-> master_host= ' Master_host_name ',
-> master_user= ' Replication_user_name ',
-> master_password= ' Replication_password ',
-> master_log_file= ' Recorded_log_file_name ',
-> master_log_pos=recorded_log_position;
For example:
mysql> Change Master to master_host= ' 10.58.91.4 ', master_user= ' repl ', master_password= ' 888888 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=385;
Note: Replication cannot use UNIX socket files and can only connect to the main library using TCP/IP.
Start from library
mysql> start slave;
If a problem occurs that cannot be connected, check to see if the server iptables port restrictions

3 Common commands

Authorize any host to access in root mode
Mysql> grant all privileges in *.* to ' root ' @ '% ' with GRANT OPTION
View Server-id
Mysql> Show variables like ' server_id ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| server_id | 0 |
+---------------+-------+
1 row in Set (0.01 sec)