Configuration File Locations
Post date: Feb 19, 2020 5:19:35 AM
Default Configuration File Locations
Execute mysqlrouter --help to see the default configuration file locations (and their availability) on your system
shell> mysqlrouter --help
The MySQL configuration file on Windows is by default called my.ini and called my.cnf on Linux or other Unix operating systems
Option Files Read on Windows Systems
C:\> echo %WINDIR%
C:\> echo %APPDATA%
Option Files Read on Unix and Unix-Like Systems
Here is a typical global option file:
[client]port=3306socket=/tmp/mysql.sock[mysqld]port=3306socket=/tmp/mysql.sockkey_buffer_size=16Mmax_allowed_packet=128M[mysqldump]quick
Here is a typical user option file:
[client]# The following password will be sent to all standard MySQL clientspassword="my password"[mysql]no-auto-rehashconnect_timeout=2
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
[mysqld]
bind-address = 192.168.0.1
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
Binary Log
Binlog Dump
Relay Log
Each replica keeps a record of the binary log coordinates: the file name and position within the file that it has read and processed from the source. Multiple replicas can be connected to the source and executing different parts of the same binary log
Binlog dumo is a thread on a master server for sending binary log contents to a slave server
The relay log is a set of log files created by a replica during replication. Containing a record of events that affect the data or structure; thus, mysqlbinlog can be used to display its contents. It consists of a set of relay log files and an index file containing a list of all relay log
files.
Relay-log-index
Relay-log-info-file
General log - all queries - see VARIABLE general_log
Slow log - queries slower than long_query_time - slow_query_log_file
Binlog - for replication and backup - log_bin_basename
Relay log - also for replication
general errors - mysqld.err
start/stop - mysql.log (not very interesting) - log_error
InnoDB redo log - iblog*
What Is Partitioning? Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables, but still get treated as a single table by the SQL layer.
RANGE partitioning. This type of partitioning assigns rows to partitions based on column values falling within a given range.
CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL, email VARCHAR(35),joined DATE NOT NULL)
PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE);
LIST partitioning. Similar to partitioning by RANGE,except that the partition is selected based on columns matching one of a set of discrete values
CREATE TABLE employees ( id INT NOT NULL,fname VARCHAR(30), lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31',job_code INT,
store_id INT)
PARTITION BY LIST(store_id) ( PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16));
HASH partitioning. With this type of partitioning, a partition is selected based on the value returned by a user-defined expression that operates on column values in rows to be inserted into the table.
The function may consist of any expression valid in MySQL that yields a nonnegative integer value
CREATE TABLE employees ( id INT NOT NULL,fname VARCHAR(30),lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT)
PARTITION BY HASH(store_id) PARTITIONS 4;
KEY partitioning. This type of partitioning is similar to partitioning by HASH,except that only one or more columns to be evaluated are supplied, and the MySQL server provides its own hashing function. These columns can contain other than integer values,
CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL
PARTITION BY KEY(joined)
PARTITIONS 6;