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

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;