tag:blogger.com,1999:blog-18061011523418305682024-03-14T10:21:26.490-05:00Another MySQL DBAKeith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.comBlogger113125tag:blogger.com,1999:blog-1806101152341830568.post-3618478297412578812024-02-24T18:20:00.002-06:002024-02-24T18:20:35.534-06:00MariaDB Encryption ( data at rest ) <p><span style="font-family: arial;">Here is a simple example showing a couple options for MariaDB Encryption. </span></p><p><span style="font-family: arial;">You have to consider what you want to encrypt . The data communication (data in transit) or the data on the instance (data at rest). </span></p><p><span style="font-family: arial;">This post is going to focus on the data at rest option using a AWS free tier node running on Amazon Linux. I will be using the world database on 2 different instances to show updating current tables with encryption as well as new loading tables to be auto-encrypted. </span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;">1st we will start with installs.. quick and simple just for this demo. </span></p><div style="text-align: left;"><span style="font-family: courier;"> # yum -y install mariadb105-server<br /></span><span style="font-family: courier;"># vi /etc/my.cnf.d/mariadb-server.cnf<br /></span><span style="font-family: courier;"># cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id <br /></span><span style="font-family: courier;">server_id=100</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"># cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id<br /></span><span style="font-family: courier;">server_id=200<br /></span><span style="font-family: courier;"># systemctl start mariadb.service<br /></span><span style="font-family: courier;"># mysql<br /></span><span style="font-family: courier;">MariaDB [(none)]> status<br /></span><span style="font-family: courier;">--------------<br /></span><span style="font-family: courier;">mysql Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using EditLine wrapper</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">Connection id:<span style="white-space: pre;"> </span>3<br /></span><span style="font-family: courier;">Current database:<br /></span><span style="font-family: courier;">Current user:<span style="white-space: pre;"> </span>root@localhost<br /></span><span style="font-family: courier;">SSL:<span style="white-space: pre;"> </span>Not in use<br /></span><span style="font-family: courier;">Current pager:<span style="white-space: pre;"> </span>stdout<br /></span><span style="font-family: courier;">Using outfile:<span style="white-space: pre;"> </span>''<br /></span><span style="font-family: courier;">Using delimiter:<span style="white-space: pre;"> </span>;<br /></span><span style="font-family: courier;">Server:<span style="white-space: pre;"> </span>MariaDB<br /></span><span style="font-family: courier;">Server version:<span style="white-space: pre;"> </span>10.5.23-MariaDB MariaDB Server<br /></span><span style="font-family: courier;">Protocol version:<span style="white-space: pre;"> </span>10<br /></span><span style="font-family: courier;">Connection:<span style="white-space: pre;"> </span>Localhost via UNIX socket<br /></span><span style="font-family: courier;">Server characterset:<span style="white-space: pre;"> </span>latin1<br /></span><span style="font-family: courier;">Db characterset:<span style="white-space: pre;"> </span>latin1<br /></span><span style="font-family: courier;">Client characterset:<span style="white-space: pre;"> </span>utf8<br /></span><span style="font-family: courier;">Conn. characterset:<span style="white-space: pre;"> </span>utf8<br /></span><span style="font-family: courier;">UNIX socket:<span style="white-space: pre;"> </span>/var/lib/mysql/mysql.sock<br /></span><span style="font-family: courier;">Uptime:<span style="white-space: pre;"> </span>27 sec</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [(none)]> select @@server_id;<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| @@server_id |<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| 100 |<br /></span><span style="font-family: courier;">+-------------+</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [(none)]> select @@server_id;<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| @@server_id |<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| 200 |<br /></span><span style="font-family: courier;">+-------------+</span></div><div style="text-align: left;"><br /></div><p style="text-align: left;"><span style="font-family: arial;"><br /></span></p><p style="text-align: left;"><span style="font-family: arial;">We will load the world db into server_id 100 instance. </span></p><p style="text-align: left;"><span style="font-family: arial;"><br /></span></p><div style="text-align: left;"><span style="font-family: courier;"># mysql < world.sql<br /></span><span style="font-family: courier;"># mysql<br /></span><span style="font-family: courier;">Welcome to the MariaDB monitor. Commands end with ; or \g.<br /></span><span style="font-family: courier;">Your MariaDB connection id is 5<br /></span><span style="font-family: courier;">Server version: 10.5.23-MariaDB MariaDB Server</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.</span></div><div style="text-align: left;"><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [(none)]> select @@server_id;<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| @@server_id |<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| 100 |<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">1 row in set (0.000 sec)</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [(none)]> show databases;<br /></span><span style="font-family: courier;">+--------------------+<br /></span><span style="font-family: courier;">| Database |<br /></span><span style="font-family: courier;">+--------------------+<br /></span><span style="font-family: courier;">| information_schema |<br /></span><span style="font-family: courier;">| mysql |<br /></span><span style="font-family: courier;">| performance_schema |<br /></span><span style="font-family: courier;">| world |<br /></span><span style="font-family: courier;">+--------------------+<br /></span><span style="font-family: courier;">4 rows in set (0.000 sec)</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [(none)]> select @@server_id;<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| @@server_id |<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">| 200 |<br /></span><span style="font-family: courier;">+-------------+<br /></span><span style="font-family: courier;">1 row in set (0.000 sec)</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [(none)]> show databases;<br /></span><span style="font-family: courier;">+--------------------+<br /></span><span style="font-family: courier;">| Database |<br /></span><span style="font-family: courier;">+--------------------+<br /></span><span style="font-family: courier;">| information_schema |<br /></span><span style="font-family: courier;">| mysql |<br /></span><span style="font-family: courier;">| performance_schema |<br /></span><span style="font-family: courier;">+--------------------+<br /></span><span style="font-family: courier;">3 rows in set (0.000 sec) </span></div><p style="text-align: left;"><br /></p><p style="text-align: left;"><span style="font-family: arial;">Now we can see that currently, both instances are not using encryption. </span></p><p style="text-align: left;"><br /></p><div style="text-align: left;"><span style="font-family: courier;">MariaDB [(none)]> select @@server_id;<br />+-------------+<br />| @@server_id |<br />+-------------+<br />| 100 |<br />+-------------+<br />1 row in set (0.000 sec)<br />MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;<br />Empty set (0.000 sec)</span></div><div style="text-align: left;"><span style="font-family: courier;"><br />MariaDB [(none)]> select @@server_id;<br />+-------------+<br />| @@server_id |<br />+-------------+<br />| 200 |<br />+-------------+<br />1 row in set (0.000 sec)<br />MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;<br />Empty set (0.000 sec)</span></div><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p style="text-align: left;"><span style="font-family: arial;">Now across both systems, I am going to set up Random Keys and encrypt them. </span></p><p style="text-align: left;"><span style="font-family: courier;"># mkdir /etc/mysql/</span></p><div style="text-align: left;"><span style="font-family: courier;"># mkdir /etc/mysql/encryption/<br /></span><span style="font-family: courier;"># (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile<br /></span><span style="font-family: courier;"># (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile<br /></span><span style="font-family: courier;"># (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"># cat /etc/mysql/encryption/keyfile<br /></span><span style="font-family: courier;">1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5<br /></span><span style="font-family: courier;">2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096<br /></span><span style="font-family: courier;">100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"># cat /etc/mysql/encryption/keyfile<br /></span><span style="font-family: courier;">1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a<br /></span><span style="font-family: courier;">2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec<br /></span><span style="font-family: courier;">100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key<br /></span><span style="font-family: courier;">openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/encryption/keyfile.key -in /etc/mysql/encryption/keyfile -out /etc/mysql/encryption/keyfile.enc</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"> ls -ltr /etc/mysql/encryption/<br /></span><span style="font-family: courier;">total 12<br /></span><span style="font-family: courier;">-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile<br /></span><span style="font-family: courier;">-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key<br /></span><span style="font-family: courier;">-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc</span></div><p style="text-align: left;"><span style="font-family: courier;"></span></p><p style="text-align: left;"><br /></p><p style="text-align: left;"><span style="font-family: arial;">Now we can set up the cnf file to enable the plugin as well as options for encryption. </span></p><p style="text-align: left;"><br /></p><div style="text-align: left;"><span style="font-family: courier;"># vi /etc/my.cnf.d/mariadb-server.cnf<br />[mariadb]<br />## File Key Management<br />plugin_load_add = file_key_management<br />loose_file_key_management_filename = /etc/mysql/encryption/keyfile<br />loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc<br />loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key<br />loose_file_key_management_encryption_algorithm = AES_CBC<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">## InnoDB/XtraDB Encryption Setup<br />innodb_encrypt_log = ON<br />innodb_encrypt_temporary_tables=ON<br />innodb_encryption_threads=4<br />innodb_encrypt_tables=ON<br />innodb_default_encryption_key_id=1<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">## Aria Encryption Setup<br />aria_encrypt_tables = ON<br /><br />## Temp & Log Encryption<br />encrypt-tmp-disk-tables = 1<br />encrypt-tmp-files = 1<br />encrypt_binlog = ON</span></div><div style="text-align: left;"><span style="font-family: courier;"><br /></span></div><div style="text-align: left;"><span style="font-family: arial;">Restart MariaDB so we can check on the current status. </span></div><div style="text-align: left;"><span style="font-family: courier;"><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">systemctl restart mariadb.service</span></div><div style="text-align: left;"><span style="font-family: courier;">mysql</span></div><div style="text-align: left;"><span style="font-family: courier;"><div>MariaDB [(none)]> select @@server_id;</div><div>+-------------+</div><div>| @@server_id |</div><div>+-------------+</div><div>| 100 |</div><div>+-------------+</div><div>1 row in set (0.001 sec)</div><div><br /></div><div>MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;</div><div>+----------------------------+-------------------+----------------+</div><div>| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |</div><div>+----------------------------+-------------------+----------------+</div><div>| innodb_system | 1 | 1 |</div><div>| mysql/innodb_table_stats | 1 | 1 |</div><div>| mysql/innodb_index_stats | 1 | 1 |</div><div>| mysql/transaction_registry | 1 | 1 |</div><div>| mysql/gtid_slave_pos | 1 | 1 |</div><div>| world/city | 1 | 1 |</div><div>| world/country | 1 | 1 |</div><div>| world/countrylanguage | 1 | 1 |</div><div>+----------------------------+-------------------+----------------+</div><div>8 rows in set (0.000 sec)</div><div><br /></div><div><div>MariaDB [(none)]> select @@server_id;</div><div>+-------------+</div><div>| @@server_id |</div><div>+-------------+</div><div>| 200 |</div><div>+-------------+</div><div>1 row in set (0.000 sec)</div><div><br /></div><div>MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;</div><div>+----------------------------+-------------------+----------------+</div><div>| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |</div><div>+----------------------------+-------------------+----------------+</div><div>| innodb_system | 1 | 1 |</div><div>| mysql/innodb_index_stats | 1 | 1 |</div><div>| mysql/gtid_slave_pos | 1 | 1 |</div><div>| mysql/innodb_table_stats | 1 | 1 |</div><div>| mysql/transaction_registry | 1 | 1 |</div><div>+----------------------------+-------------------+----------------+</div><div>5 rows in set (0.000 sec)</div></div></span></div><p style="text-align: left;"><br /></p><p style="text-align: left;"><span style="font-family: arial;">Load up the world data into the server_id 200 instance as well. </span></p><div style="text-align: left;"><span style="font-family: courier;"># mysql < world.sql<br /># mysql <br />MariaDB [(none)]> select @@server_id;<br />+-------------+<br />| @@server_id |<br />+-------------+<br />| 200 |<br />+-------------+<br />1 row in set (0.000 sec)<br />MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;<br />+----------------------------+-------------------+----------------+<br />| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |<br />+----------------------------+-------------------+----------------+<br />| innodb_system | 1 | 1 |<br />| mysql/innodb_index_stats | 1 | 1 |<br />| mysql/gtid_slave_pos | 1 | 1 |<br />| mysql/innodb_table_stats | 1 | 1 |<br />| mysql/transaction_registry | 1 | 1 |<br />| world/city | 1 | 1 |<br />| world/country | 1 | 1 |<br />| world/countrylanguage | 1 | 1 |<br />+----------------------------+-------------------+----------------+<br />8 rows in set (0.000 sec)</span></div><h3 style="text-align: left;"><br /></h3><p style="text-align: left;"><span style="font-family: arial;">According to the information_schema.INNODB_TABLESPACES_ENCRYPTION we are encrypted now. However, they do not show that at the schema level. While they say it is encrypted if showing up in the </span><span style="font-family: arial;">INNODB_TABLESPACES_ENCRYPTION table, I would rather be sure and have it seen in the table and on the schema. </span></p><div style="text-align: left;"><span style="font-family: courier;">MariaDB [(none)]> show create table world.city\G<br /></span><span style="font-family: courier;">*************************** 1. row ***************************<br /></span><span style="font-family: courier;"> Table: city<br /></span><span style="font-family: courier;">Create Table: CREATE TABLE `city` (<br /></span><span style="font-family: courier;"> `ID` int(11) NOT NULL AUTO_INCREMENT,<br /></span><span style="font-family: courier;"> `Name` char(35) NOT NULL DEFAULT '',<br /></span><span style="font-family: courier;"> `CountryCode` char(3) NOT NULL DEFAULT '',<br /></span><span style="font-family: courier;"> `District` char(20) NOT NULL DEFAULT '',<br /></span><span style="font-family: courier;"> `Population` int(11) NOT NULL DEFAULT 0,<br /></span><span style="font-family: courier;"> PRIMARY KEY (`ID`),<br /></span><span style="font-family: courier;"> KEY `CountryCode` (`CountryCode`),<br /></span><span style="font-family: courier;"> CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)<br /></span><span style="font-family: courier;">) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci<br /></span><span style="font-family: courier;">1 row in set (0.001 sec)</span></div><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p style="text-align: left;"><span style="font-family: arial;">Up to this point, you can see that both instances have been accounted for in the </span><span style="font-family: arial;">INNODB_TABLESPACES_ENCRYPTION schema after the restart or loading of the schema and data. </span></p><p style="text-align: left;"><span style="font-family: arial;">So... a few table alters will help here... </span></p><p style="text-align: left;"><br /></p><div style="text-align: left;"><span style="font-family: courier;">MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;<br /></span><span style="font-family: courier;">Query OK, 0 rows affected (0.074 sec)<br /></span><span style="font-family: courier;">Records: 0 Duplicates: 0 Warnings: 0</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;<br /></span><span style="font-family: courier;">Query OK, 0 rows affected (0.031 sec)<br /></span><span style="font-family: courier;">Records: 0 Duplicates: 0 Warnings: 0</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;">MariaDB [world]> ALTER TABLE countrylanguage ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;<br /></span><span style="font-family: courier;">Query OK, 0 rows affected (0.033 sec)<br /></span><span style="font-family: courier;">Records: 0 Duplicates: 0 Warnings: 0</span></div><div style="text-align: left;"><span style="font-family: courier;"><br /></span></div><div style="text-align: left;"><span style="font-family: courier;"><div>MariaDB [world]> show create table city\G</div><div>*************************** 1. row ***************************</div><div> Table: city</div><div>Create Table: CREATE TABLE `city` (</div><div> `ID` int(11) NOT NULL AUTO_INCREMENT,</div><div> `Name` char(35) NOT NULL DEFAULT '',</div><div> `CountryCode` char(3) NOT NULL DEFAULT '',</div><div> `District` char(20) NOT NULL DEFAULT '',</div><div> `Population` int(11) NOT NULL DEFAULT 0,</div><div> PRIMARY KEY (`ID`),</div><div> KEY `CountryCode` (`CountryCode`),</div><div> CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)</div><div>) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1</div><div>1 row in set (0.000 sec)</div></span></div><p style="text-align: left;"><span style="font-family: courier;"></span></p><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p style="text-align: left;"><span style="font-family: arial;">This is simple and etc so far.. now we need to enable binlogs and double check more. </span></p><div style="text-align: left;"><span style="font-family: courier;">vi /etc/my.cnf.d/mariadb-server.cnf<br />log_bin=demo<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin<br />log_bin=demo<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;"># systemctl restart mariadb.service<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">MariaDB [(none)]> select @@server_id;<br />+-------------+<br />| @@server_id |<br />+-------------+<br />| 100 |<br />+-------------+<br />1 row in set (0.000 sec)<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">MariaDB [(none)]> show master status\G<br />*************************** 1. row ***************************<br /> File: demo.000001<br /> Position: 363<br /> Binlog_Do_DB:<br />Binlog_Ignore_DB:<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">MariaDB [(none)]> select @@server_id;<br />+-------------+<br />| @@server_id |<br />+-------------+<br />| 200 |<br />+-------------+<br />1 row in set (0.000 sec)<br /><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">MariaDB [(none)]> show master status\G<br />*************************** 1. row ***************************<br /> File: demo.000001<br /> Position: 363<br /> Binlog_Do_DB:<br />Binlog_Ignore_DB:<br />1 row in set (0.000 sec)</span></div><p style="text-align: left;"><span style="font-family: courier;"></span></p><p style="text-align: left;"><span style="font-family: courier;"></span></p><p style="text-align: left;"><span style="font-family: courier;"></span></p><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p style="text-align: left;"><span style="font-family: arial;">Checking via a look at the binlogs.... </span></p><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p><span style="font-family: courier;">mariadb-binlog--base64-output=DECODE-ROWS --verbose demo.000001</span></p><p><span style="font-family: courier;">/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;</span></p><p><span style="font-family: courier;">/*!40019 SET @@session.max_insert_delayed_threads=0*/;</span></p><p><span style="font-family: courier;">/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</span></p><p><span style="font-family: courier;">DELIMITER /*!*/;</span></p><p><span style="font-family: courier;"># at 4</span></p><p><span style="font-family: courier;">#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 <span style="white-space: pre;"> </span>Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup</span></p><p><span style="font-family: courier;"># Warning: this binlog is either in use or was not closed properly.</span></p><p><span style="font-family: courier;">ROLLBACK/*!*/;</span></p><p><span style="font-family: courier;"># at 256</span></p><p><span style="font-family: courier;"># Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537</span></p><p><span style="font-family: courier;"># The rest of the binlog is encrypted!</span></p><p><span style="font-family: courier;">ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240</span></p><p><span style="font-family: courier;">DELIMITER ;</span></p><p><span style="font-family: courier;"># End of log file</span></p><p><span style="font-family: courier;">ROLLBACK /* added by mysqlbinlog */;</span></p><p><span style="font-family: courier;">/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</span></p><p><span style="font-family: courier;"></span></p><p><span style="font-family: courier;">/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;</span></p><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p style="text-align: left;"><span style="font-family: arial;">Good to see that it says it is being encrypted now. </span></p><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><div style="text-align: left;"><span style="font-family: courier;">MariaDB [world]> create table city2 like city;<br /></span><span style="font-family: courier;">Query OK, 0 rows affected (0.013 sec)</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">MariaDB [world]> insert into city2 select * from city;<br /></span><span style="font-family: courier;">Query OK, 4079 rows affected (0.078 sec)<br /></span><span style="font-family: courier;">Records: 4079 Duplicates: 0 Warnings: 0</span><span style="font-family: courier;"><br /></span><span style="font-family: courier;"><br /></span></div><div style="text-align: left;"><span style="font-family: courier;">MariaDB [world]> show create table city2\G<br /></span><span style="font-family: courier;">*************************** 1. row ***************************<br /></span><span style="font-family: courier;"> Table: city2<br /></span><span style="font-family: courier;">Create Table: CREATE TABLE `city2` (<br /></span><span style="font-family: courier;"> `ID` int(11) NOT NULL AUTO_INCREMENT,<br /></span><span style="font-family: courier;"> `Name` char(35) NOT NULL DEFAULT '',<br /></span><span style="font-family: courier;"> `CountryCode` char(3) NOT NULL DEFAULT '',<br /></span><span style="font-family: courier;"> `District` char(20) NOT NULL DEFAULT '',<br /></span><span style="font-family: courier;"> `Population` int(11) NOT NULL DEFAULT 0,<br /></span><span style="font-family: courier;"> PRIMARY KEY (`ID`),<br /></span><span style="font-family: courier;"> KEY `CountryCode` (`CountryCode`)<br /></span><span style="font-family: courier;">) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1<br /></span><span style="font-family: courier;">1 row in set (0.000 sec)</span></div><p style="text-align: left;"><span style="font-family: courier;"></span></p><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><p style="text-align: left;"><span style="font-family: arial;">I want to see these transactions though in the binlog.. how? You can use mariadb_binlog along with --read-from-remote-server to be able to see the data in the logs...</span></p><p style="text-align: left;"><br /></p><div style="text-align: left;"><span style="font-family: courier;">mariadb-binlog --base64-output=DECODE-ROWS --verbose --read-from-remote-server demo.000001 | more<br />/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;<br />/*!40019 SET @@session.max_insert_delayed_threads=0*/;<br />/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;<br />DELIMITER /*!*/;<br /># at 4<br />#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 <span style="white-space: pre;"> </span>Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup<br />ROLLBACK/*!*/;<br /># at 256<br />#240225 0:06:06 server id 100 end_log_pos 296 CRC32 0x0c89f3bb <span style="white-space: pre;"> </span>Ignorable<br /># Ignorable event type 164 (Start_encryption)<br /># at 296<br />#240225 0:06:06 server id 100 end_log_pos 325 CRC32 0x535776a2 <span style="white-space: pre;"> </span>Gtid list []<br /># at 325<br />#240225 0:06:06 server id 100 end_log_pos 363 CRC32 0x2ac4a61b <span style="white-space: pre;"> </span>Binlog checkpoint demo.000001<br /># at 363<br />#240225 0:09:40 server id 100 end_log_pos 405 CRC32 0x93e10dc4 <span style="white-space: pre;"> </span>GTID 0-100-1 ddl<br />/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;<br />/*!100001 SET @@session.gtid_domain_id=0*//*!*/;<br />/*!100001 SET @@session.server_id=100*//*!*/;<br />/*!100001 SET @@session.gtid_seq_no=1*//*!*/;<br /># at 405<br />#240225 0:09:40 server id 100 end_log_pos 501 CRC32 0x39269040 <span style="white-space: pre;"> </span>Query<span style="white-space: pre;"> </span>thread_id=5<span style="white-space: pre;"> </span>exec_time=0<span style="white-space: pre;"> </span>error_code=0<br />use `world`/*!*/;<br />SET TIMESTAMP=1708819780/*!*/;<br />SET @@session.pseudo_thread_id=5/*!*/;<br />SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_<br />for_timestamp=0/*!*/;<br />SET @@session.sql_mode=1411383296/*!*/;<br />SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;<br />/*!\C utf8 *//*!*/;<br />SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;<br />SET @@session.lc_time_names=0/*!*/;<br />SET @@session.collation_database=DEFAULT/*!*/;<br />create table city2 like city<br />/*!*/;<br /># at 501<br />#240225 0:09:49 server id 100 end_log_pos 543 CRC32 0xde82b753 <span style="white-space: pre;"> </span>GTID 0-100-2 trans<br />/*!100001 SET @@session.gtid_seq_no=2*//*!*/;<br />START TRANSACTION<br />/*!*/;<br /># at 543<br /># at 602<br />#240225 0:09:49 server id 100 end_log_pos 602 CRC32 0x05bbb9e6 <span style="white-space: pre;"> </span>Annotate_rows:<br />#Q> insert into city2 select * from city<br />#240225 0:09:49 server id 100 end_log_pos 661 CRC32 0x9e0b4e0d <span style="white-space: pre;"> </span>Table_map: `world`.`city2` mapped to number 21<br /># at 661</span></div><p style="text-align: left;"><br /></p><p style="text-align: left;"><span style="font-family: arial;">Hopefully, this can at least help get you started .... </span></p><p style="text-align: left;"><span style="font-family: courier;"><br /></span></p><h3 style="text-align: left;"><span style="font-family: courier;">Res</span>ources:</h3><p></p><p>https://mariadb.com/kb/en/securing-mariadb-encryption/ </p>Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-32373088926063471242020-11-12T09:10:00.006-06:002021-06-02T21:12:34.557-05:00Using your FRM file to get Schema and then import idb files..<p> This is a topic that overall you never should have to do... Why? Because you created backups right... You have tested and know the backups work so you can just restore those backups and get your lost schema and related data... </p>
<p>However that one instance in the corner office.. you never got around to setting up.. it not that important... just crashed and now you realize how you actually do use it... </p>
<p>All is not lost.. </p>
<p>MySQL released their MySQL utilities awhile back and since been replaced more with the MySQL Shell. </p>
<p></p>
<ul style="text-align: left;">
<li><a href="https://downloads.mysql.com/archives/utilities">https://downloads.mysql.com/archives/utilities</a> </li>
<li><a href="https://dev.mysql.com/downloads/shell/">https://dev.mysql.com/downloads/shell/</a></li>
</ul>
<div>mysqlfrm is still very handy though when needing to pull out the schema from an FRM file in a quick and simple command and it is a simple install. </div>
<div><br /></div>
<div>
<div class="codebox">
<code>
mysqlfrm --diagnostic city.frm<br />
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.<br />
# Reading .frm file for city.frm:<br />
# The .frm file is a TABLE.<br />
# CREATE TABLE Statement:<br />
<br />
CREATE TABLE `city` (<br />
`ID` int(11) NOT NULL AUTO_INCREMENT,<br />
`Name` char(160) DEFAULT NULL,<br />
`CountryCode` char(12) NOT NULL,<br />
`District` char(80) NOT NULL,<br />
`Population` int(11) NOT NULL,<br />
PRIMARY KEY `PRIMARY` (`ID`),<br />
KEY `CountryCode` (`CountryCode`),<br />
KEY `popkey` (`Population`)<br />
) ENGINE=InnoDB;<br />
<br />
#...done.<br />
</code>
</div>
</div>
<div><br /></div>
<div><br /></div>
<div>So now that you have the schema you lost... rebuild the DB or table. For the sake of the example, I will say we just lost the city data from the world DB. </div>
<p></p>
<div><span style="font-family: monospace;">$ cp </span><span style="font-family: monospace;">city.ibd</span><span style="font-family: monospace;"> /tmp/ </span></div>
<div><span style="font-family: monospace;"><br /></span></div>
<div>
<div class="codebox">
<code>
$ cp city.ibd /tmp/ <br />
mysql> LOCK TABLES city WRITE;<br />
mysql> ALTER TABLE city DISCARD TABLESPACE;<br />
<br />
cp city.ibd /edb/local/mysql/data/rundeck/<br />
chown tmdba:dba /edb/local/mysql/data/rundeck/city.ibd<br />
<br />
mysql> ALTER TABLE city IMPORT TABLESPACE;<br />
mysql> UNLOCK TABLES;<br />
mysql> SELECT COUNT(*) FROM city;<br />
<br />
</code>
</div>
</div>Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-88475432845078589652020-09-21T12:36:00.001-05:002021-06-02T21:10:39.931-05:00MySQL mysql_config_editor & expect<p>This is just a note to help out anyone that might want to use the mysql_config_editor command in their automation tools. </p><p>the mysql_config_editor does not take a password argument so automation tools that might have before set your password in the .my.cnf file trying to use mysql_config_editor fails. </p><p>It is possible and quite simple though with the expect tool. </p><p></p><div class="</code></div>ox"><code></code></div><p></p><p> yum -y install expect </p><p></p><p>it works for apt-get also. </p><p><br /></p><p>So in this example, I will show a simple bash script version. </p><p>1st.. my login path does not work... </p><p></p><div class="</code></div>ox"><code></code></div><p></p><p>mysql --login-path=local</p><p>ERROR 1045 (28000): Access denied for user</p><p></p><p><br /></p><p>Set this with expect </p><p>You would execute this via your bash script. </p><p></p><div class="</code></div>ox"><code></code></div><p></p><p>expect <<EOD</p><p>spawn mysql_config_editor set --login-path=local --host=localhost --user=root --password </p><p>expect "password"</p><p>send -- "<PASSWORD>\r"</p><p>interact</p><p>EOD</p><p></p><p><br /></p><p>Now it works...</p><p></p>
<p></p><p>mysql --login-path=local</p><p>Welcome to the MySQL monitor. Commands end with ; or \g.</p><p>Your MySQL connection id is 1002</p><p></p><p><br /></p><p><br /></p>Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-74927285641314290062020-03-15T17:08:00.001-05:002020-03-15T17:08:29.514-05:00MySQL & Dockers...a simple set up<div dir="ltr" style="text-align: left;" trbidi="on">
MySQL & Dockers... are not new concepts, people have been moving to Dockers for some time now. For someone who is just moving to this for development, it can have a few hurdles.<br />
<br />
While MySQL works just fine running locally, if you are testing code across different versions of MySQL it is nice to have several versions easily available.<br />
<br />
One option for years has been of course <a href="https://mysqlsandbox.net/">https://mysqlsandbox.net/</a> by Giuseppe Maxia. This is a very valid solution to be able to get several instances up and test replication and etc etc.<br />
<br />
Dockers are now also another often used scenario when it comes to testing across different versions of MySQL. The following will just go over some of the steps to get several versions installed easily. I use OSX so these examples are for OSX.<br />
<br />
You need Docker to start and of course and Docker Desktop is a handy tool for you to be able to get access easily.<br />
<br />
<ul style="text-align: left;">
<li><a href="https://docs.docker.com/docker-for-mac/install/">https://docs.docker.com/docker-for-mac/install/</a></li>
<li><a href="https://docs.docker.com/docker-for-windows/">https://docs.docker.com/docker-for-windows/</a></li>
</ul>
<div>
Once I had Docker set up I can get my environment ready for MySQL. </div>
<div>
<br /></div>
<div>
Here I created a Docker folder that contains the MySQL data directories, Config files as well as the mysql-files directory if I needed it. </div>
<div class="codebox">
<code>
</code><br />
<div>
<code>mkdir ~/Docker ;</code></div>
<code>
</code>
<br />
<div>
<code>mkdir ~/Docker/mysql_data;</code></div>
<code>
</code>
<div>
<code>mkdir ~/Docker/mysql-files;</code></div>
<code>
<div>
mkdir ~/Docker/cnf;</div>
</code></div>
<div>
<br /></div>
<div>
Now inside mysql_data</div>
<br />
<div class="codebox">
<code>
</code><br />
<div>
<div>
<code>cd ~/Docker/mysql_data;</code></div>
<div>
<code>mkdir 8.0;</code></div>
<div>
<code>mkdir 5.7;</code></div>
<div>
<code>mkdir 5.6;</code></div>
<div>
<code>mkdir 5.5;</code></div>
</div>
</div>
<div>
<br /></div>
<div>
<br />
<div>
Now I set up simple cnf files for this example. The primary thing to note is the bind-address. This is set to ensure it is opened up for us to reach MySQL outside of the docker. You can also notice that these files can be used to set up additional configuration information as you see fit per MySQL docker instance. </div>
<div>
<br /></div>
<br />
<div class="codebox">
<code>
</code><br />
<div>
<div>
<code>cd ~/Docker/cnf;</code></div>
<div>
<code><br /></code></div>
<div>
<div>
<code>cat my.8.0.cnf</code></div>
<div>
<code>[mysqld]</code></div>
<div>
<code>pid-file = /var/run/mysqld/mysqld.pid</code></div>
<div>
<code>socket = /var/run/mysqld/mysqld.sock</code></div>
<div>
<code>datadir = /var/lib/mysql</code></div>
<div>
<code>secure-file-priv= /var/lib/mysql-files</code></div>
<div>
<code># Disabling symbolic-links is recommended to prevent assorted security risks</code></div>
<div>
<code>symbolic-links=0</code></div>
<div>
<code>bind-address = 0.0.0.0</code></div>
<div>
<code>port=3306</code></div>
<div>
<code>server-id=80</code></div>
<div>
<code><br /></code></div>
<div>
<code><br /></code></div>
<div>
<code># Custom config should go here</code></div>
<div>
<code>!includedir /etc/mysql/conf.d/</code></div>
</div>
<div>
<code><br /></code></div>
<div>
<div>
<code> cat my.5.7.cnf</code></div>
<div>
<code>[mysqld]</code></div>
<div>
<code>bind-address = 0.0.0.0</code></div>
<div>
<code>server-id=57</code></div>
<div>
<code>max_allowed_packet=32M</code></div>
</div>
<div>
<code><br /></code></div>
<div>
<code>$ cat my.5.6.cnf</code></div>
<div>
<div>
<code>[mysqld]</code></div>
<div>
<code>bind-address = 0.0.0.0</code></div>
<div>
<code>server-id=56</code></div>
</div>
<div>
<code><br /></code></div>
<div>
<code>$ cat my.5.5.cnf</code></div>
<div>
<div>
<code>[mysqld]</code></div>
<div>
<code>bind-address = 0.0.0.0</code></div>
<div>
<code>server-id=55</code></div>
</div>
</div>
</div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
OK so now that we have configuration files set up, We need to build the dockers. A few things to note for the build commands. </div>
<div>
<br /></div>
<div>
--name We set a named reference for the docker. </div>
<div>
<br /></div>
<div>
Here we are mapping the configuration files, data directory and mysql-files directories to the docker . This allows us to adjust the my.cnf file and etc easily. </div>
<div>
-v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf </div>
<div>
-v ~/Docker/mysql_data/8.0:/var/lib/mysql</div>
<div>
-v ~/Docker/mysql-files:/var/lib/mysql-files</div>
<div>
<br /></div>
<div>
We want to be able to reach these MySQL instances outside of the docker so we need to publish and map the port accordingly. </div>
<div>
-p 3306:3306 This means 3306 local to 3306 inside docker</div>
<div>
-p 3307:3306 This means 3307 local to 3306 inside docker</div>
<div>
-p 3308:3306 This means 3308 local to 3306 inside docker</div>
<div>
-p 3309:3306 This means 3309 local to 3306 inside docker</div>
<div>
<br /></div>
<div>
Then we also pass a couple of environment variables. </div>
<div>
-e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here></div>
<div>
<br /></div>
<div>
So putting it all together...</div>
<div>
<br /></div>
<div>
<div class="codebox">
<code>
</code><br />
<div>
<div>
<div>
<code>docker run --restart always --name mysql8.0 -v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/8.0:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3306:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:8.0</code></div>
<div>
<code><br /></code></div>
<div>
<code>docker run --restart always --name mysql5.7 -v ~/Docker/cnf/my.5.7.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.7:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3307:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.7</code></div>
<div>
<code><br /></code></div>
<div>
<code>docker run --restart always --name mysql5.6 -v ~/Docker/cnf/my.5.6.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.6:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3308:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.6</code></div>
<div>
<code><br /></code></div>
<div>
<code>docker run --restart always --name mysql5.5 -v ~/Docker/cnf/my.5.5.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.5:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3309:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.5</code></div>
</div>
</div>
</div>
</div>
<div>
<br /></div>
<div>
After each execution of the above commands, you should get an id returned. </div>
<div>
example: 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316</div>
<div>
<br /></div>
<div>
You can start/restart and access each docker terminal easily via the Docker Desktop or just keep note of the related IDs and you execute via the terminal.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5HMCaey092-p4IObD0ynigBuz7alr9Y0zu8xWzVZrzIZkMX0sRGZTeiL9yq002jyDThF2hEjQbP-C5t3Isf2PD-RMHHMzLZLXOXc6S_reEEDpDP3b1rLyBzPFwpr6xiA4fFHGOXFboXE/s1600/Screen+Shot+2020-03-15+at+4.01.31+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="485" data-original-width="853" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5HMCaey092-p4IObD0ynigBuz7alr9Y0zu8xWzVZrzIZkMX0sRGZTeiL9yq002jyDThF2hEjQbP-C5t3Isf2PD-RMHHMzLZLXOXc6S_reEEDpDP3b1rLyBzPFwpr6xiA4fFHGOXFboXE/s400/Screen+Shot+2020-03-15+at+4.01.31+PM.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
The Docker Desktop also shows you all the variables you passed so you can validate. </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9YJlR_FxBkHBZZOhN6SV3OANVJhV6hg7GBDEAwCF9eA82WtMqXJ_le5dGNmoyeu8UidUrMvRdxLn68vbOb0cR3yBM99f5-RnrFhyzMRx11vV4mWlrDDATamwmrRP4AzjUSMjueFVWicU/s1600/Screen+Shot+2020-03-15+at+4.04.04+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="53" data-original-width="249" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9YJlR_FxBkHBZZOhN6SV3OANVJhV6hg7GBDEAwCF9eA82WtMqXJ_le5dGNmoyeu8UidUrMvRdxLn68vbOb0cR3yBM99f5-RnrFhyzMRx11vV4mWlrDDATamwmrRP4AzjUSMjueFVWicU/s1600/Screen+Shot+2020-03-15+at+4.04.04+PM.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
You can of course also access the CLI here, stop and start or destroy it easily. </div>
<br /></div>
<div>
<br /></div>
<div class="codebox">
<code>
</code><br />
<div>
<div class="p1" style="font-family: Menlo; font-size: 11px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
<code><span class="s1" style="font-variant-ligatures: no-common-ligatures;">$ docker exec -it 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316 /bin/sh; exit</span></code></div>
<div class="p1" style="font-family: Menlo; font-size: 11px; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
<code><span class="s1" style="font-variant-ligatures: no-common-ligatures;"># mysql -p </span></code></div>
</div>
<code>
</code></div>
<div>
<br /></div>
<div>
If the Docker container is already running you can now access MySQL via your localhost terminal.</div>
<div>
<div class="codebox">
<code>
</code><br />
<div>
<div class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal;">
<code><span class="s1" style="font-family: "menlo"; font-size: 11px;">$ </span><span style="font-family: "menlo";"><span style="font-size: 11px; font-variant-ligatures: no-common-ligatures;">mysql --host=localhost --protocol=tcp --port=3306 -p -u root </span></span></code></div>
</div>
<code>
</code></div>
</div>
<div>
<br /></div>
<div>
Now if you are having any access issues remember to ensure that MySQL accounts are correct and that your ports and mapping correctly. </div>
<div>
<ul style="text-align: left;">
<li>Lost connection to MySQL server at 'reading initial communication packet'</li>
<li>ERROR 1045 (28000): Access denied for user 'root'@'192.168.0.5' (using password: YES)</li>
</ul>
</div>
<div>
<br />
Now you can see that all are up and available and the server Ids match what we set per cnf file eariler.</div>
<div>
<br /></div>
<div class="codebox">
<code>
$ mysql --host=localhost --protocol=tcp --port=3306 -e "Select @@hostname, @@version, @@server_id "<br />
+--------------+-----------+-------------+<br />
| @@hostname | @@version | @@server_id |<br />
+--------------+-----------+-------------+<br />
| 58e9663afe8d | 8.0.19 | 80 |<br />
+--------------+-----------+-------------+<br />
$ mysql --host=localhost --protocol=tcp --port=3307 -e "Select @@hostname, @@version, @@server_id "<br />
+--------------+-----------+-------------+<br />
| @@hostname | @@version | @@server_id |<br />
+--------------+-----------+-------------+<br />
| b240917f051a | 5.7.29 | 57 |<br />
+--------------+-----------+-------------+<br />
$ mysql --host=localhost --protocol=tcp --port=3308 -e "Select @@hostname, @@version, @@server_id "<br />
+--------------+-----------+-------------+<br />
| @@hostname | @@version | @@server_id |<br />
+--------------+-----------+-------------+<br />
| b4653850cfe9 | 5.6.47 | 56 |<br />
+--------------+-----------+-------------+<br />
$ mysql --host=localhost --protocol=tcp --port=3309 -e "Select @@hostname, @@version, @@server_id "<br />
+--------------+-----------+-------------+<br />
| @@hostname | @@version | @@server_id |<br />
+--------------+-----------+-------------+<br />
| 22e169004583 | 5.5.62 | 55 |<br />
+--------------+-----------+-------------+<br />
</code></div>
<div>
<br /></div>
<div>
<br /></div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com1tag:blogger.com,1999:blog-1806101152341830568.post-17052870321776753032019-07-13T00:23:00.001-05:002019-07-13T00:23:44.511-05:00MySQL How do you restore tablespace<div dir="ltr" style="text-align: left;" trbidi="on">
MySQL How do you restore tablespace?<br />
<br />
This is not new information but I have not covered it much so addressing it now for those that need it.<br />
<br />
If you lose your ibd files... you lose your data. So if you have a copy of one available.. or even if you are syncing from another database you can still import it. What/how do you lose tablespace?<br />
<br />
Here is a simple example to recover tablespace.<br />
<br />
<div class="codebox">
<code> <br />
<br />
mysql> Create database demo;<br />
<br />
mysql> use demo;<br />
<br />
mysql> CREATE TABLE `demotable` (<br />
-> `id` int(11) NOT NULL AUTO_INCREMENT,<br />
-> `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,<br />
-> PRIMARY KEY (`id`)<br />
-> ) ENGINE=InnoDB;<br />
</code></div>
<br />
<br />
Now we store some data...<br />
<br />
<div class="codebox">
<code> <br />
mysql> INSERT INTO demotable (id) VALUES (NULL);<br />
Query OK, 1 row affected (0.10 sec)<br />
<br />
mysql> INSERT INTO demotable (id) VALUES (NULL);<br />
Query OK, 1 row affected (0.08 sec)<br />
<br />
mysql> SELECT * FROM demotable;<br />
+----+---------------------+<br />
| id | dts |<br />
+----+---------------------+<br />
| 1 | 2019-07-12 23:31:34 |<br />
| 2 | 2019-07-12 23:31:35 |<br />
+----+---------------------+<br />
2 rows in set (0.00 sec)<br />
</code></div>
<br />
<br />
OK now lets break it..<br />
<br />
<div class="codebox">
<code> <br />
# systemctl stop mysqld<br />
# cd /var/lib/mysql/demo/<br />
# ls -ltr<br />
total 80<br />
-rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd<br />
# mv demotable.ibd /tmp/<br />
<br />
# systemctl start mysqld<br />
# mysql demo<br />
<br />
mysql> show tables;<br />
+----------------+<br />
| Tables_in_demo |<br />
+----------------+<br />
| demotable |<br />
+----------------+<br />
1 row in set (0.00 sec)<br />
<br />
mysql> desc demotable;<br />
+-------+-----------+------+-----+-------------------+-----------------------------------------------+<br />
| Field | Type | Null | Key | Default | Extra |<br />
+-------+-----------+------+-----+-------------------+-----------------------------------------------+<br />
| id | int(11) | NO | PRI | NULL | auto_increment |<br />
| dts | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |<br />
+-------+-----------+------+-----+-------------------+-----------------------------------------------+<br />
2 rows in set (0.01 sec)<br />
<br />
mysql> INSERT INTO demotable (id) VALUES (NULL);<br />
ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.<br />
</code></div>
<br />
<br />
Broken and lost tablespace... Now we can recover it..<br />
<br />
<div class="codebox">
<code> <br />
demo]# cp /tmp/demotable.ibd .<br />
<br />
mysql> ALTER TABLE demotable DISCARD TABLESPACE;<br />
<br />
demo]# cp /tmp/demotable.ibd .<br />
demo]# ls -ltr<br />
total 112<br />
-rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd<br />
demo]# chown mysql:mysql demotable.ibd<br />
demo]# mysql demo<br />
mysql> ALTER TABLE demotable IMPORT TABLESPACE;<br />
ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it<br />
<br />
mysql> REPAIR TABLE demotable;<br />
+----------------+--------+----------+---------------------------------------------------------+<br />
| Table | Op | Msg_type | Msg_text |<br />
+----------------+--------+----------+---------------------------------------------------------+<br />
| demo.demotable | repair | note | The storage engine for the table doesn't support repair |<br />
+----------------+--------+----------+---------------------------------------------------------+<br />
</code></div>
<br />
<br />
Notice now we also got another error.. This is usually tied to space available to tmpdir, and repair doesn't work for .ibd anyway.<br />
<br />
<div class="codebox">
<code> <br />
mysql> select @@tmpdir;<br />
+----------+<br />
| @@tmpdir |<br />
+----------+<br />
| /tmp |<br />
+----------+<br />
<br />
# vi /etc/my.cnf<br />
tmpdir=/var/lib/mysql-files/<br />
<br />
# systemctl restart mysqld<br />
# mysql demo<br />
</code></div>
<br />
<br />
OK used the mysql-files directory just for example.<br />
Now we can try again.<br />
<br />
<div class="codebox">
<code> <br />
mysql> ALTER TABLE demotable IMPORT TABLESPACE;<br />
Query OK, 0 rows affected, 1 warning (0.61 sec)<br />
<br />
mysql> INSERT INTO demotable (id) VALUES (NULL);<br />
Query OK, 1 row affected (0.11 sec)<br />
<br />
mysql> SELECT * FROM demotable;<br />
+----+---------------------+<br />
| id | dts |<br />
+----+---------------------+<br />
| 1 | 2019-07-12 23:31:34 |<br />
| 2 | 2019-07-12 23:31:35 |<br />
| 3 | 2019-07-12 23:56:08 |<br />
+----+---------------------+<br />
</code></div>
<br />
<br />
OK worked.<br />
Now, this is all nice and simple if you just have one table. But what about 100s...<br />
<br />
Automate it, of course, and use your information_schema to help.<br />
<br />
Make a few more copies for test.<br />
<div class="codebox">
<code> <br />
mysql> create table demotable1 like demotable;<br />
Query OK, 0 rows affected (0.51 sec)<br />
<br />
mysql> create table demotable2 like demotable;<br />
Query OK, 0 rows affected (1.04 sec)<br />
<br />
mysql> create table demotable3 like demotable;<br />
Query OK, 0 rows affected (0.74 sec)<br />
<br />
mysql> create table demotable4 like demotable;<br />
Query OK, 0 rows affected (2.21 sec)<br />
</code></div>
<br />
<br />
break them all..<br />
<div class="codebox">
<code> <br />
demo]# mv *.ibd /tmp/<br />
</code></div>
<br />
<br />
Now using your information_schema.tables table, you can build out all the commands you will need.<br />
<div class="codebox">
<code> <br />
# vi build_discard.sql<br />
# cat build_discard.sql<br />
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';<br />
<br />
# vi build_import.sql<br />
# cat build_import.sql<br />
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';<br />
</code></div>
<br />
<br />
<div class="codebox">
<code> <br />
# mysql -N < build_import.sql > import_tablespace.sql<br />
# mysql -N < build_discard.sql | mysql demo<br />
<br />
demo]# cp /tmp/*.ibd .<br />
demo]# chown mysql:mysql *.ibd<br />
# systemctl restart mysqld<br />
# mysql demo < import_tablespace.sql<br />
# mysql demo<br />
<br />
mysql> INSERT INTO demotable (id) VALUES (NULL);<br />
Query OK, 1 row affected (0.08 sec)<br />
<br />
mysql> INSERT INTO demotable1 (id) VALUES (NULL);<br />
Query OK, 1 row affected (0.05 sec)<br />
<br />
mysql> INSERT INTO demotable2 (id) VALUES (NULL);<br />
Query OK, 1 row affected (0.09 sec)<br />
<br />
mysql> INSERT INTO demotable3 (id) VALUES (NULL);<br />
^[[AQuery OK, 1 row affected (0.37 sec)<br />
<br />
mysql> INSERT INTO demotable4 (id) VALUES (NULL);<br />
Query OK, 1 row affected (0.12 sec)<br />
</code><br />
<div>
</div>
</div>
<br />
<br />
And it worked.<br />
<br />
<br /></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-48687121926272279972019-07-12T23:43:00.000-05:002019-07-12T23:43:10.990-05:00MySQL Binlogs:: How to recover<div dir="ltr" style="text-align: left;" trbidi="on">
So I realized I had not made a post about this after this situation that recently came up.<br />
<br />
Here is the scenario: A backup was taken at midnight, they used MySQL dumps per database. Then at ten am the next day the database crashed. A series of events happened before I was called in, but they got it up to a version of the database with MyISAM tables and the IBD files missing from the tablespace.<br />
<br />
So option 1, restoring from backup would get us to midnight and we would lose hours of data. Option 2, we reimport the 1000's of ibd files and keep everything. Then we had option 3, restore from backup, then apply the binlogs for recent changes.<br />
<br />
To make it more interesting, they didn't have all of the ibd files I was told, and I did see some missing. So not sure how that was possible but option 2 became an invalid option. They, of course, wanted the least data loss possible, so we went with option 3.<br />
<br />
To do this safely I started up another instance of MySQL under port 3307. This allowed me a safe place to work while traffic had read access to the MyISAM data on the port 3306 instance.<br />
<br />
Once all the backup dump files uncompressed and imported into the 3307 instance I was able to focus on the binlog files.<br />
<br />
At first this concept sounds much harder risky than it really is. It is actually pretty straight forward and simple.<br />
<br />
So first you have to find the data your after. A review of the binlog files gives you a head start as to what files are relevant. In my case, somehow they managed to reset the binlog so the 117 file had 2 date ranges within it.<br />
<br />
First for binlog review, the following command outputs the data in human-readable format.<br />
<div class="codebox">
<code>
mysqlbinlog --defaults-file=/root/.my.cnf --base64-output=DECODE-ROWS --verbose mysql-bin.000117 > review_mysql-bin.000117.sql<br />
</code></div>
<br />
*Note... Be careful running the above command. Notice I have it dumping the file directly in same location as binlog. So VALIDATE that your file name is valid. This mysql-bin.000117.sql is different than this mysql-bin.000117 .sql . You will loose your binlog with the 2nd option and a space before .sql.<br />
<br />
Now to save the data so it can be applied. Since I had several binlogs I created a file and I wanted to double-check the time ranges anyway.<br />
<br />
<div class="codebox">
<code>
<br />
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > binlog_restore.sql<br />
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql<br />
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql<br />
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 >> binlog_restore.sql<br />
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql<br />
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql<br />
<br />
mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"<br />
<br />
</code></div>
<br />
Now I applied all the data from those binlogs for the given time ranges. The client double-checked all data and was very happy to have it all back.<br />
<br />
Several different options existed for this situation, this happened to workout best with the client.<br />
<br />
Once the validated all was ok on the restored version it was a simple stop both databases, moved the data directories (wanted to keep the datadir defaults intact) , chown the directories just to be safe and start up MySQL. Now the restored instance was up on port 3306.<br />
<br />
<br />
<style type="text/css">
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000}
p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; min-height: 13.0px}
span.s1 {font-variant-ligatures: no-common-ligatures}
</style></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-12377167469959483892019-06-17T07:56:00.000-05:002019-06-17T07:56:30.809-05:00MySQL Group Replication<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
So MySQL's group replication came out with MySQL 5.7. Now that is has been out a little while people are starting to ask more about it.<br />
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication.html">https://dev.mysql.com/doc/refman/8.0/en/group-replication.html</a></li>
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html">https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html</a></li>
</ul>
Below is an example of how to set this up and a few pain point examples as I poked around with it.<br />
I am using three different servers,<br />
<br />
Server CENTOSA
<br />
<div class="codebox">
<code>
<br />
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; <br />
Query OK, 0 rows affected (0.02 sec)<br />
<br />
vi my.cnf<br />
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
<br />
server_id=1<br />
gtid_mode=ON<br />
enforce_gtid_consistency=ON<br />
binlog_checksum=NONE<br />
<br />
log_bin=binlog<br />
log_slave_updates=ON<br />
binlog_format=ROW<br />
master_info_repository=TABLE<br />
relay_log_info_repository=TABLE<br />
<br />
transaction_write_set_extraction=XXHASH64<br />
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"<br />
group_replication_start_on_boot=off<br />
group_replication_local_address= "192.168.111.17:33061"<br />
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"<br />
group_replication_bootstrap_group=off<br />
<br />
mysql> SET SQL_LOG_BIN=0;<br />
mysql> CREATE USER repl@'%' IDENTIFIED BY 'replpassword';<br />
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';<br />
mysql> FLUSH PRIVILEGES;<br />
mysql> SET SQL_LOG_BIN=1;<br />
<br />
<br />
CHANGE MASTER TO <br />
MASTER_USER='repl', <br />
MASTER_PASSWORD='replpassword' <br />
FOR CHANNEL 'group_replication_recovery';<br /><br />
<br />
mysql> SET GLOBAL group_replication_bootstrap_group=ON;<br />
Query OK, 0 rows affected (0.00 sec)<br /><br />
<br />
mysql> START GROUP_REPLICATION;<br />
Query OK, 0 rows affected (3.11 sec)<br /><br />
<br />
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;<br />
Query OK, 0 rows affected (0.00 sec)<br /><br />
<br />
mysql> SELECT * FROM performance_schema.replication_group_members \G<br /><br />
*************************** 1. row ***************************<br />
CHANNEL_NAME: group_replication_applier<br />
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1<br />
MEMBER_HOST: centosa<br />
MEMBER_PORT: 3306<br />
MEMBER_STATE: ONLINE<br />
MEMBER_ROLE: PRIMARY<br />
MEMBER_VERSION: 8.0.15<br />
<br />
</code></div>
So now we can add more servers.<br />
Server CENTOSB<br />
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html" target="_blank">https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html</a></li>
</ul>
<br />
<div class="codebox">
<code>
vi my.cnf<br />
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"<br />
server_id=2<br />
gtid_mode=ON<br />
enforce_gtid_consistency=ON<br />
binlog_checksum=NONE<br />
<br />
log_bin=binlog<br />
log_slave_updates=ON<br />
binlog_format=ROW<br />
master_info_repository=TABLE<br />
relay_log_info_repository=TABLE<br /><br />
<br />
transaction_write_set_extraction=XXHASH64<br />
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"<br />
group_replication_start_on_boot=off<br />
group_replication_local_address= "192.168.111.89:33061"<br />
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"<br />
group_replication_bootstrap_group=off<br />
<br />
mysql> CHANGE MASTER TO <br />
MASTER_USER='repl', <br />
MASTER_PASSWORD='replpassword' <br />
FOR CHANNEL 'group_replication_recovery';<br />
Query OK, 0 rows affected, 2 warnings (0.02 sec)<br />
<br />
mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;<br />
Query OK, 0 rows affected (0.02 sec)<br />
<br />
mysql> START GROUP_REPLICATION;<br />
Query OK, 0 rows affected (4.03 sec)<br />
<br />
mysql> SELECT * FROM performance_schema.replication_group_members;<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |<br />
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | RECOVERING | SECONDARY | 8.0.15 |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
2 rows in set (0.00 sec)<br />
</code></div>
<br />
<br />
Server CENTOSC<br />
<ul style="text-align: left;">
<li> <a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html" target="_blank">https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html</a></li>
</ul>
<br />
<div class="codebox">
<code>
vi my.cnf<br />
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"<br />
server_id=3<br />
gtid_mode=ON<br />
enforce_gtid_consistency=ON<br />
binlog_checksum=NONE<br />
log_bin=binlog<br />
log_slave_updates=ON<br />
binlog_format=ROW<br />
master_info_repository=TABLE<br />
relay_log_info_repository=TABLE<br />
<br />
transaction_write_set_extraction=XXHASH64<br />
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"<br />
group_replication_start_on_boot=off<br />
group_replication_local_address= "192.168.111.124:33061"<br />
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"<br />
group_replication_bootstrap_group=off<br />
<br />
mysql> CHANGE MASTER TO <br />
-> MASTER_USER='repl', <br />
-> MASTER_PASSWORD='replpassword' <br />
-> FOR CHANNEL 'group_replication_recovery';<br />
Query OK, 0 rows affected, 2 warnings (0.02 sec)<br />
<br />
mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;<br />
Query OK, 0 rows affected (0.02 sec)<br />
<br />
mysql> START GROUP_REPLICATION;<br />
Query OK, 0 rows affected (3.58 sec)<br />
mysql> SELECT * FROM performance_schema.replication_group_members \G<br />
*************************** 1. row ***************************<br />
CHANNEL_NAME: group_replication_applier<br />
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1<br />
MEMBER_HOST: centosa<br />
MEMBER_PORT: 3306<br />
MEMBER_STATE: ONLINE<br />
MEMBER_ROLE: PRIMARY<br />
MEMBER_VERSION: 8.0.15<br />
<br />
*************************** 2. row ***************************<br />
CHANNEL_NAME: group_replication_applier<br />
MEMBER_ID: 572ca2fa-5eff-11e9-8df9-08002712f4b1<br />
MEMBER_HOST: centosb<br />
MEMBER_PORT: 3306<br />
MEMBER_STATE: ONLINE<br />
MEMBER_ROLE: SECONDARY<br />
MEMBER_VERSION: 8.0.15<br />
<br />
*************************** 3. row ***************************<br />
CHANNEL_NAME: group_replication_applier<br />
MEMBER_ID: c5f3d1d2-8dd8-11e9-858d-08002773d1b6<br />
MEMBER_HOST: centosc<br />
MEMBER_PORT: 3306<br />
MEMBER_STATE: ONLINE<br />
MEMBER_ROLE: SECONDARY<br />
MEMBER_VERSION: 8.0.15<br />
3 rows in set (0.00 sec)<br />
</code></div>
<br />
<br />
So this is all great but it doesn't always mean they go online, they can often sit in recovery mode.<br />
I have seen this fail with MySQL crashes so far so need to ensure it stable.<br />
<div class="codebox">
<code>
mysql> create database testcentosb;<br>
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement<br>
</code> </div>
Side Note to address some of those factors --
<br />
<div class="codebox">
<code>
mysql> START GROUP_REPLICATION;<br />
ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.<br />
<br />
mysql> reset slave all;<br />
Query OK, 0 rows affected (0.03 sec)<br />
<br />
</code> </div>
-- Then start over from Change master command
<br />
<div class="codebox">
<code>
mysql> START GROUP_REPLICATION;<br />
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.<br />
<br />
[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.111.17:33061 on local port: 33061.'<br />
[ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: c5f3d1d2-8dd8-11e9-858d-08002773d1b6:1-4 >
<br />
[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'<br />
</code></div>
<br />
<a href="https://ronniethedba.wordpress.com/2017/04/22/this-member-has-more-executed-transactions-than-those-present-in-the-group/" target="_blank">https://ronniethedba.wordpress.com/2017/04/22/this-member-has-more-executed-transactions-than-those-present-in-the-group/</a> </div>
<br />
<br />
<div class="codebox">
<code>
[ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the recovery process of Group Replication. The server will leave the group.'<br />
[ERROR] [MY-013173] [Repl] Plugin group_replication reported: 'The plugin encountered a critical error and will abort: Fatal error during execution of Group Replication'<br />
<br />
SELECT * FROM performance_schema.replication_connection_status\G
</code></div>
<br />
<br />
My thoughts...<br />
Keep in mind that group replication can be set up in single primary mode or multi-node<br />
<div class="codebox">
<code>
mysql> select @@group_replication_single_primary_mode\G<br />
*************************** 1. row ***************************<br />
@@group_replication_single_primary_mode: 1<br />
<br />
mysql> create database testcentosb;<br />
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement<br />
</code></div>
you will of course get an error if you write to none primary node.
<br />
<div class="codebox">
<div class="p1">
<span class="s1"><br /></span></div>
<style type="text/css">
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000}
span.s1 {font-variant-ligatures: no-common-ligatures}
</style>
<br />
<div class="p1">
<span class="s1">group-replication-single-primary-mode=off <-- added to the cnf files. </span></div>
<div class="p1">
<span class="s1">mysql> SELECT * FROM performance_schema.replication_group_members;</span></div>
<div class="p1">
<span class="s1">+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+</span></div>
<div class="p1">
<span class="s1">| CHANNEL_NAME<span class="Apple-converted-space"> </span>| MEMBER_ID<span class="Apple-converted-space"> </span>| MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |</span></div>
<div class="p1">
<span class="s1">+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+</span></div>
<div class="p1">
<span class="s1">| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa <span class="Apple-converted-space"> </span>|<span class="Apple-converted-space"> </span>3306 | RECOVERING <span class="Apple-converted-space"> </span>| PRIMARY <span class="Apple-converted-space"> </span>| 8.0.15 <span class="Apple-converted-space"> </span>|</span></div>
<div class="p1">
<span class="s1">| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb <span class="Apple-converted-space"> </span>|<span class="Apple-converted-space"> </span>3306 | ONLINE <span class="Apple-converted-space"> </span>| PRIMARY <span class="Apple-converted-space"> </span>| 8.0.15 <span class="Apple-converted-space"> </span>|</span></div>
<div class="p1">
<span class="s1">| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc <span class="Apple-converted-space"> </span>|<span class="Apple-converted-space"> </span>3306 | RECOVERING <span class="Apple-converted-space"> </span>| PRIMARY <span class="Apple-converted-space"> </span>| 8.0.15 <span class="Apple-converted-space"> </span>|</span></div>
<div class="p1">
<span class="s1">+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+</span></div>
<style type="text/css">
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000}
span.s1 {font-variant-ligatures: no-common-ligatures}
</style>
<br />
<div class="p1">
<span class="s1">3 rows in set (0.00 sec)</span></div>
</div>
<br />
<br />
It is now however if you use Keepalived, MySQL router, ProxySQL etc to handle your traffic to automatically roll over in case of a failover. We can see from below it failed over right away when I stopped the primary.<br />
<br />
<div class="codebox">
<code>
mysql> SELECT * FROM performance_schema.replication_group_members ;<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |<br />
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | SECONDARY | 8.0.15 |<br />
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
3 rows in set (0.00 sec)<br />
<br />
[root@centosa]# systemctl stop mysqld<br />
<br />
mysql> SELECT * FROM performance_schema.replication_group_members ;<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |<br />
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
2 rows in set (0.00 sec)<br />
<br />
[root@centosa]# systemctl start mysqld<br />
[root@centosa]# mysql<br />
mysql> START GROUP_REPLICATION;<br />
Query OK, 0 rows affected (3.34 sec)<br />
<br />
mysql> SELECT * FROM performance_schema.replication_group_members ;<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | SECONDARY | 8.0.15 |<br />
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |<br />
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
3 rows in set (0.00 sec)<br />
</code></div>
<br />
<br />
Now the recovery was still an issue, as it is would not simply join back. Had to review all accounts and steps again but I did get it back eventually.<br />
<br />
<div class="codebox">
<code>
mysql> SELECT * FROM performance_schema.replication_group_members;<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | SECONDARY | 8.0.15 |<br />
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |<br />
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |<br />
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+<br />
3 rows in set (0.00 sec)<br />
</code></div>
<br />
<br />
I need to test more with this as I am not 100% sold yet as to needing this as I lean towards Galera replication still.<br />
<br />
URLS of Interest<br />
<br />
<br />
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication.html">https://dev.mysql.com/doc/refman/8.0/en/group-replication.html</a></li>
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html">https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html</a></li>
<li><a href="http://datacharmer.blogspot.com/2017/01/mysql-group-replication-vs-multi-source.html">http://datacharmer.blogspot.com/2017/01/mysql-group-replication-vs-multi-source.html</a> </li>
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-launching.html">https://dev.mysql.com/doc/refman/8.0/en/group-replication-launching.html</a></li>
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-instances.html">https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-instances.html</a></li>
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html">https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html</a></li>
<li><a href="https://ronniethedba.wordpress.com/2017/04/22/how-to-setup-mysql-group-replication/">https://ronniethedba.wordpress.com/2017/04/22/how-to-setup-mysql-group-replication/</a></li>
<li><a href="https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04">https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04</a> </li>
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_group_seeds">https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_group_seeds</a> </li>
<li><a href="https://bugs.mysql.com/bug.php?id=90534" target="_blank">https://bugs.mysql.com/bug.php?id=90534 </a></li>
<li><a href="https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/">https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/</a></li>
<li><a href="https://lefred.be/content/mysql-group-replication-is-sweet-but-can-be-sour-if-you-misunderstand-it/">https://lefred.be/content/mysql-group-replication-is-sweet-but-can-be-sour-if-you-misunderstand-it/</a></li>
<li><a href="https://www.youtube.com/watch?v=IfZK-Up03Mw">https://www.youtube.com/watch?v=IfZK-Up03Mw</a></li>
<li><a href="https://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/">https://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/</a></li>
<br />
<br /></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-86879269161641333292019-06-03T13:08:00.000-05:002019-06-03T13:08:14.838-05:00Max_connections 214 4.15.0-46-generic #49-Ubuntu<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
So the issue of max_connections dropping from the value set in your my.cnf file down to 214 has been around for a little while on Ubuntu.<br />
<br />
As an example, it was noted here back in 2015<br />
<br />
<ul style="text-align: left;">
<li><a href="https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/">https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/</a></li>
</ul>
<br />
<br />
I ran into this again recently and was resolved with the following steps.<br />
<br />
<br />
<div class="codebox">
<code>
# cp /lib/systemd/system/mysql.service /etc/systemd/system/<br />
# cd /etc/systemd/system/<br />
# vi mysql.service <br />
<br />
LimitNOFILE=infinity<br />
LimitMEMLOCK=infinity<br />
<br />
# systemctl daemon-reload<br />
# systemctl restart mysql<br />
</code></div>
<br />
<br />
Once those steps completed the MySQL connections were stable at the given parameter in the my.cnf file. </div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-55736643292459893362019-04-25T08:52:00.000-05:002019-04-25T08:52:10.373-05:00Slack<div dir="ltr" style="text-align: left;" trbidi="on">
Just a simple post here to give support to the slack community channels.<br />
<br />
If you have not joined some of these... this is a great way to communicate with the community.<br />
<br />
MySQL -- <a href="https://mysqlcommunity.slack.com/">https://mysqlcommunity.slack.com</a> <a href="https://mysql-casual.slack.com/">https://mysql-casual.slack.com/</a><br />
<br />
PostgreSQL - <a href="https://postgresteam.slack.com/">https://postgresteam.slack.com</a> <a href="https://postgres-slack.herokuapp.com/">https://postgres-slack.herokuapp.com/</a><br />
<br />
MongoDB -- <a href="https://mongo-db.slack.com/">https://mongo-db.slack.com</a><br />
<br />
Of course, Freenode is still available as well -- <a href="http://chat.freenode.net/">http://chat.freenode.net</a> (<a href="https://freenode.net/">https://freenode.net/</a>)</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-51608428388894523442019-04-14T17:14:00.000-05:002019-04-14T17:14:39.863-05:00Simple KeepaliveD set up<div dir="ltr" style="text-align: left;" trbidi="on">
So keepalived has been around for quite a while now .... however it is still a mystery to many.<br />
So this is a very simple example of how keepalived can work with MySQL. Hopefully, this can help those with questions.<br />
<br />
We will have a Simple master to slave set up. Meaning.. we write to one unless we have failover to the second for some event.<br />
<br />
1st - install keepalived<br />
<br />
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style>
<br />
<div class="p1">
<span class="s1"># yum search keepalived</span></div>
<div class="p1">
<span class="s1"><b>keepalived</b>.x86_64 : Load balancer and high availability service</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>Name and summary matches <b>only</b>, use "search all" for everything.</span></div>
<div class="p1">
<span class="s1"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; min-height: 13.0px} span.s1 {font-variant-ligatures: no-common-ligatures} </style> </span></div>
<div class="p1">
<span class="s1"># yum -y install keepalived</span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;">You should now have an config file </span></span></div>
<div class="p1">
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;"><br /></span></span></div>
<div class="p1">
<span class="s1"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style> </span></div>
<div class="p1">
<span class="s1"># ls -ltr /etc/keepalived/keepalived.conf<span class="Apple-converted-space"> </span></span></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"><br /></span></span></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;">Keep the original as you always backup .. right....</span></span></span></div>
<div class="p1">
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style>
</div>
<div class="p1">
<span class="s1"># cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.orig</span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;">So you need to figure out an ipaddress you can use for your virtual ip. I picked 192.168.0.123 for this example. </span></div>
<div class="p1">
<br /></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;">Next, we will set up a script to be used for our new config file. </span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;"><br /></span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;">A few things I did here..</span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;">I left a .cnf file for keepalived and a log all in the /etc/keepalived.</span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;">This makes it simple for the example so you can do this or use your own cnf files.</span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;"><br /></span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;">A script: </span><br />
<span style="font-family: "times"; font-size: small;"><br /></span>
<div class="p1">
<span class="s1">cat /etc/keepalived/keepalived_check.sh<span class="Apple-converted-space"> </span></span></div>
<div class="p1">
<span class="s1">#!/bin/bash</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"># monitor mysql status</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"># if this node mysql is dead</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"># and its slave is alive , then stop its keepalived. The other node will bind the IP.</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">export MYSQL_HOME=/etc/keepalived/</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">export PATH=$MYSQL_HOME/bin:$PATH</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">mysql="/usr/bin/mysql"</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">mysqladmin="/usr/bin/mysqladmin"</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">delay_file="$MYSQL_HOME/slave_delay_second.log"</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">slave_host=$1</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">ALIVE=$($mysqladmin --defaults-file=$MYSQL_HOME/.my.localhost.cnf<span class="Apple-converted-space"> </span>ping | grep alive | wc -l );</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">REMOTEALIVE=$($mysqladmin --defaults-file=$MYSQL_HOME/.my.remotehost.cnf<span class="Apple-converted-space"> </span>ping | grep alive | wc -l );</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">if [[ $ALIVE -ne 1 ]]</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">then</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">#echo "MySQL is down"</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>if [[ $REMOTEALIVE -eq 1 ]]</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>then</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">#<span class="Apple-converted-space"> </span>echo "Shutdown keep alive "</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>systemctl stop keepalived <span class="Apple-converted-space"> </span></span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"># <span class="Apple-converted-space"> </span>echo " keepalived stop "</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>fi</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">#else</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">#echo "MySQL is up"</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">#date</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">fi</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; min-height: 13.0px} span.s1 {font-variant-ligatures: no-common-ligatures} </style>
</div>
<div class="p1">
<span class="s1">exit 0 #all done</span></div>
</div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span style="font-family: "times"; font-size: small;">New config file</span><br />
<span style="font-family: "times"; font-size: small;"><br /></span>
<div class="p1">
<span class="s1">cat /etc/keepalived/keepalived.conf</span></div>
<div class="p1">
<span class="s1">global_defs {</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>notification_email {</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>anothermysqldba@gmail.com<span class="Apple-converted-space"> </span></span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>}</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>notification_email_from anothermysqldba@gmail.com<span class="Apple-converted-space"> </span></span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>smtp_server localhost</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>smtp_connect_timeout 30</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>}</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">vrrp_script check_mysql {</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>script "/etc/keepalived/keepalived_check.sh "</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>interval 2</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>weight 2</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">}</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1">vrrp_instance VI_1 {</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>state MASTER</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>interface enp0s8<span class="Apple-converted-space"> </span># <--- WHAT INTERFACE NAME HOLDS YOUR REAL IP /sbin/ifconfig</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span># found with ip link show</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>virtual_router_id 51</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>priority 101</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>advert_int 1</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>nopreempt<span class="Apple-converted-space"> </span># only needed on higher priority node</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>authentication {</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>auth_type PASS</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>auth_pass 1111</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>}</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>track_script {</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>check_mysql</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>}</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>virtual_ipaddress {</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>192.168.0.123<span class="Apple-converted-space"> </span></span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>}</span></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<div class="p2">
<span class="s1"></span><br /></div>
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; min-height: 13.0px} span.s1 {font-variant-ligatures: no-common-ligatures} </style>
<br />
<div class="p1">
<span class="s1">}</span></div>
</div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;">This is all great but does it work....</span></span></div>
<div class="p1">
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;"><br /></span></span>
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;">So we have 2 hosts</span></span><br />
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;"><br /></span></span>
<div class="p1">
<span class="s1">[root@centosa keepalived]# hostname</span></div>
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style> </span></span><br />
<div class="p1">
<span class="s1">centosa</span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1">[root@centosb keepalived]# hostname</span></div>
<div class="p1">
<span class="s1"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style> </span></div>
<div class="p1">
<span class="s1">centosb</span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><span style="font-family: times; font-size: small; font-variant-ligatures: normal;">Start keepalived </span></span></div>
<div class="p1">
<br /></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# systemctl status keepalived</span></div>
<div class="p1">
<span class="s1">● keepalived.service - LVS and VRRP High Availability Monitor</span></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)</span></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>Active: inactive (dead)</span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# systemctl restart keepalived</span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# systemctl status keepalived</span></div>
<div class="p1">
<span class="s2"><b>●</b></span><span class="s1"> keepalived.service - LVS and VRRP High Availability Monitor</span></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>Loaded: loaded (/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)</span></div>
<div class="p1">
<span class="s1"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #2fb41d} span.s1 {font-variant-ligatures: no-common-ligatures} span.s2 {font-variant-ligatures: no-common-ligatures; color: #2fb41d} span.s3 {font-variant-ligatures: no-common-ligatures; color: #000000} </style> </span></div>
<div class="p2">
<span class="s3"><span class="Apple-converted-space"> </span>Active: </span><span class="s1"><b>active (running)</b></span></div>
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;"><br /></span></span></div>
<div class="p1">
<div class="p1">
<span class="s1">[root@centosa keepalived]# ssh 192.168.0.123 'hostname'</span></div>
<div class="p1">
<span class="s1">root@192.168.0.123's password:<span class="Apple-converted-space"> </span></span></div>
<span class="s1"><span style="font-family: "times"; font-size: small; font-variant-ligatures: normal;"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style> </span></span><br />
<div class="p1">
<span class="s1">centosa</span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><span style="font-family: times; font-size: small; font-variant-ligatures: normal;">Prove the connections work already</span></span></div>
<div class="p1">
<span class="s1"><span style="font-family: times; font-size: small; font-variant-ligatures: normal;"><br /></span></span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.101 <span class="Apple-converted-space"> </span>-e "select @@hostname"</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| @@hostname |</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| centosb<span class="Apple-converted-space"> </span>|</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.102 <span class="Apple-converted-space"> </span>-e "select @@hostname"</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| @@hostname |</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| centosa<span class="Apple-converted-space"> </span>|</span></div>
<div class="p1">
<span class="s1"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style> </span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><span style="font-family: times; font-size: small; font-variant-ligatures: normal;">Double check that it is running... </span></span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# systemctl status keepalived | grep active</span></div>
<div class="p1">
<span class="s1"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} span.s2 {font-variant-ligatures: no-common-ligatures; color: #b42419} </style> </span></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>Active: </span><span class="s2"><b>active</b></span><span class="s1"> </span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1">[root@centosb keepalived]# systemctl status keepalived | grep active</span></div>
<div class="p1">
<span class="s1"> <style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} span.s2 {font-variant-ligatures: no-common-ligatures; color: #b42419} </style> </span></div>
<div class="p1">
<span class="s1"><span class="Apple-converted-space"> </span>Active: </span><span class="s2"><b>active</b></span><span class="s1"> </span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><span style="font-family: times; font-size: small; font-variant-ligatures: normal;">Test current VIP .. stop mysql and watch same VIP change hosts ... </span></span></div>
<div class="p1">
<span class="s1"><span style="font-family: times; font-size: small; font-variant-ligatures: normal;"><br /></span></span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.123 <span class="Apple-converted-space"> </span>-e "select @@hostname"</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| @@hostname |</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| centosa<span class="Apple-converted-space"> </span>|</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# systemctl stop mysqld<span class="Apple-converted-space"> </span></span></div>
<div class="p1">
<span class="s1">[root@centosa keepalived]# mysql --defaults-file=.my.remotehost.cnf --host=192.168.0.123 <span class="Apple-converted-space"> </span>-e "select @@hostname"</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| @@hostname |</span></div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1">| centosb<span class="Apple-converted-space"> </span>|</span></div>
<div class="p1">
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style>
</div>
<div class="p1">
<span class="s1">+------------+</span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><br /></span></div>
<div class="p1">
<span class="s1"><br /></span></div>
</div>
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; min-height: 13.0px} span.s1 {font-variant-ligatures: no-common-ligatures} </style><style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style><br />
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; min-height: 13.0px} span.s1 {font-variant-ligatures: no-common-ligatures} </style><style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-55419917226237190232019-04-09T15:33:00.000-05:002019-04-09T15:33:08.733-05:00Sometimes the slow database.. is not the database...<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white; color: #455464; font-family: SourceSansPro, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 12px;">So I was recently asked to look into why the updated MySQL 5,.6 was slower than the older 5.5 </span><br />
<span style="background-color: white; color: #455464; font-family: SourceSansPro, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 12px;"><br /></span>
<span style="background-color: white; color: #455464; font-family: SourceSansPro, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 12px;">So I started by poking around looking over the standard variables and caches and etc.</span><br />
<span style="background-color: white; color: #455464; font-family: SourceSansPro, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 12px;"><br /></span>
<span style="background-color: white; color: #455464; font-family: SourceSansPro, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 12px;">The test case was a simple routine that took about twice as long to run on 5.6 than it did on 5.5. </span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="background-color: white; font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="background-color: white; font-size: 12px;">To add to the mix.. the 5.6 version had double the Innodb_buffer_pool_size and of course more ram overall. </span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="background-color: white; font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="background-color: white; font-size: 12px;">So I started some tests with MySQLslap...</span></span><br />
<br style="background-color: white; box-sizing: border-box; color: #455464; font-family: SourceSansPro, "Helvetica Neue", Helvetica, Arial, sans-serif; font-size: 12px;" />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Mysqlslap tests show it slower on 5.6 </span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">5.6:</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 -query=/test.sql --create-schema=applicationdata --verbose </span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Benchmark</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Average number of seconds to run all queries: 0.028 seconds</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Minimum number of seconds to run all queries: 0.019 seconds</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Maximum number of seconds to run all queries: 0.071 seconds</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Number of clients running queries: 150</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Average number of queries per client: 1</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">5.5:</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">mysqlslap --defaults-file=./.my.cnf --concurrency=150 --iterations=130 --query=/test.sql --create-schema=applicationdata --verbose </span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Benchmark</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Average number of seconds to run all queries: 0.015 seconds</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Minimum number of seconds to run all queries: 0.011 seconds</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Maximum number of seconds to run all queries: 0.024 seconds</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Number of clients running queries: 150</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Average number of queries per client: 1</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">All of this goes against the public benchmarks </span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><a href="http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-ga-vs-mysql-55-32cores.html%C2%A0" target="_blank">http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-ga-vs-mysql-55-32cores.html </a></span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">So I checked disk level --</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">5.6:</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"># dd if=/dev/zero of=test bs=1048576 count=2048</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records in</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records out</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2147483648 bytes (2.1 GB) copied, 25.7401 s, 83.4 MB/s</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"># dd if=test of=/dev/null bs=1048576</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records in</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records out</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2147483648 bytes (2.1 GB) copied, 29.1527 s, 73.7 MB/s</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">5.5:</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"># dd if=/dev/zero of=test bs=1048576 count=2048</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records in</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records out</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2147483648 bytes (2.1 GB) copied, 19.9214 seconds, 108 MB/s</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"># dd if=test of=/dev/null bs=1048576</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records in</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2048+0 records out</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">2147483648 bytes (2.1 GB) copied, 20.0243 seconds, 107 MB/s</span></span><br />
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;"><br /></span></span>
<span style="color: #455464; font-family: SourceSansPro, Helvetica Neue, Helvetica, Arial, sans-serif;"><span style="font-size: 12px;">Here the disks with 5.5 is slower regardless of MySQL. So in this case.... Look to fixing the disk speed.. MySQL was running fine and will. </span></span></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-30344443296960588912019-03-27T18:32:00.001-05:002019-03-27T18:35:32.945-05:00Every MySQL should have these variables set ...<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: white;">So over the years, we all learn more and more about what we like and use often in MySQL. </span><br />
<span style="background-color: white;"><br /></span> <span style="background-color: white;">Currently, I step in and out of a robust about of different systems. I love it being able to see how different companies use MySQL. I also see several aspect and settings that often get missed. So here are a few things I think should always be set and they not impact your MySQL database. </span><br />
<span style="background-color: white;"><br /></span> <span style="background-color: white;">At a high level:</span><br />
<div style="text-align: left;">
</div>
<br />
<ul style="text-align: left;">
<li>>Move the Slow log to a table </li>
<li>Set report_host_name </li>
<li>Set master & slaves to use tables</li>
<li>Turn off log_queries_not_using_indexes until needed </li>
<li>Side note -- USE ALGORITHM=INPLACE</li>
<li>Side note -- USE mysql_config_editor</li>
<li>Side note -- USE mysql_upgrade --upgrade-system-tables</li>
</ul>
<br />
<br />
<div style="text-align: left;">
<span style="background-color: white;"><br /></span></div>
<div style="text-align: left;">
<span style="background-color: white;"><br /></span></div>
<br />
<br />
<span style="background-color: white;"><span style="font-family: "times" , "times new roman" , serif;"><b>Move the Slow log to a table </b></span></span><br />
<div>
<span style="background-color: white; font-family: "times" , "times new roman" , serif;"><br /></span></div>
<div>
<span style="background-color: white; font-family: "times" , "times new roman" , serif;">This is a very simple process with a great return. YES you can use Percona toolkit to analyze the slow logs. However, I like being able to query against the table and find duplicate queries or by times and etc with a simple query call. </span></div>
<div>
<span style="background-color: white; font-family: "times" , "times new roman" , serif;"><br /></span></div>
<div>
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style>
<br />
<div class="codebox">
<code><br />
mysql> select count(*) from mysql.slow_log;<br />
+----------+<br />
| count(*) |<br />
+----------+<br />
| 0 |<br />
+----------+ <br />
1 row in set (0.00 sec)<br />
<br />
mysql> select @@slow_query_log,@@sql_log_off;<br />
+------------------+---------------+<br />
| @@slow_query_log | @@sql_log_off |<br />
+------------------+---------------+<br />
| 1 | 0 |<br />
+------------------+---------------+<br />
<br />
mysql> set GLOBAL slow_query_log=0;<br />
Query OK, 0 rows affected (0.04 sec)<br />
<br />
mysql> set GLOBAL sql_log_off=1;<br />
Query OK, 0 rows affected (0.00 sec)<br />
<br />
mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;<br />
Query OK, 0 rows affected (0.39 sec)<br />
<br />
mysql> set GLOBAL slow_query_log=0;<br />
Query OK, 0 rows affected (0.04 sec)<br />
<br />
mysql> set GLOBAL sql_log_off=1;<br />
Query OK, 0 rows affected (0.00 sec)<br />
<br />
mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;<br />
Query OK, 0 rows affected (0.39 sec)<br />
<br />
mysql> set GLOBAL slow_query_log=1;<br />
Query OK, 0 rows affected (0.00 sec)<br />
<br />
mysql> set GLOBAL sql_log_off=0;<br />
Query OK, 0 rows affected (0.00 sec)<br />
<br />
mysql> SET GLOBAL log_output = 'TABLE';<br />
Query OK, 0 rows affected (0.00 sec)<br />
<br />
mysql> SET GLOBAL log_queries_not_using_indexes=0;<br />
Query OK, 0 rows affected (0.00 sec)<br />
<br />
mysql> select count(*) from mysql.slow_log;<br />
+----------+<br />
| count(*) |<br />
+----------+<br />
| 0 |<br />
+----------+<br />
1 row in set (0.00 sec)
<br />
mysql> select @@slow_launch_time; <br />
+--------------------+ <br />
| @@slow_launch_time | <br />
+--------------------+ <br />
| 2 | <br />
+--------------------+ <br />
1 row in set (0.00 sec) <br />
<br />
mysql> SELECT SLEEP(10); <br />
+-----------+ <br />
| SLEEP(10) | <br />
+-----------+ <br />
| 0 | <br />
+-----------+ <br />
1 row in set (9.97 sec) <br />
<br />
mysql> select count(*) from mysql.slow_log; <br />
+----------+ <br />
| count(*) | <br />
+----------+ <br />
| 1 | <br />
+----------+ <br />
1 row in set (0.00 sec) <br />
<br />
mysql> select * from mysql.slow_log\G <br />
*************************** 1. row *************************** <br />
start_time: 2019-03-27 18:02:32 <br />
user_host: klarson[klarson] @ localhost [] <br />
query_time: 00:00:10 <br />
lock_time: 00:00:00 <br />
rows_sent: 1 <br />
rows_examined: 0<br />
db:<br />
last_insert_id: 0<br />
insert_id: 0<br />
server_id: 502<br />
sql_text: SELECT SLEEP(10)<br />
thread_id: 16586457<br />
</code></div>
<br />
Now you can truncate it or dump it or whatever you like to do with this data easily also.<br />
Note variable values into your my.cnf file to enable upon restart.<br />
<br />
<br />
<b>Set report_host_name </b><br />
<br />
<br />
<div>
This is a simple my.cnf file edit in all my.cnf files but certainly the slaves my.cnf files. On a master.. this is just set for when it ever gets flipped and becomes a slave.<br />
<div>
<br />
<br />
<div class="codebox">
<code><br />
report_host = <hostname> <or whatever you want to call it><br />
</code></div>
<div class="p1">
This allows you from the master to do<br />
<div class="p1">
<br />
<br />
<div class="codebox">
<code><br />
mysql> show slave hosts; <br />
+-----------+-------------+------+-----------+--------------------------------------+ <br />
| Server_id | Host | Port | Master_id | Slave_UUID | <br />
+-----------+-------------+------+-----------+--------------------------------------+ <br />
| 21235302 | <hostname> | 3306 | </code></div>
</div>
</div>
</div>
</div>
21235301| a55faa32-c832-22e8-b6fb-e51f15b76554 |<br />
+-----------+-------------+------+-----------+--------------------------------------+<br />
<br />
<b>Set master & slaves to use tables</b> <br />
<div class="codebox">
<code><br />
mysql> show variables like '%repository'; <br />
+---------------------------+-------+ <br />
| Variable_name | Value | <br />
+---------------------------+-------+ <br />
| master_info_repository | FILE | <br />
| relay_log_info_repository | FILE | <br />
+---------------------------+-------+ <br />
<br />
mysql_slave> stop slave;<br />
mysql_slave> SET GLOBAL master_info_repository = 'TABLE'; <br />
mysql_slave> SET GLOBAL relay_log_info_repository = 'TABLE'; <br />
mysql_slave> start slave;<br />
<br />
</code></div>
Make sure you add to my.cnf to you do not lose binlog and position at a restart. It will default to FILE otherwise.<br />
<br />
<ul style="text-align: left;">
<li>master-info-repository =TABLE </li>
<li>relay-log-info-repository =TABLE</li>
</ul>
<div class="codebox">
<code><br />
mysql> show variables like '%repository'; <br />
---------------------+-------+ <br />
| Variable_name | Value | <br />
+---------------------------+-------+ <br />
| master_info_repository | TABLE | <br />
| relay_log_info_repository | TABLE | <br />
+---------------------------+-------+ <br />
<br />
</code></div>
<br />
All data is available in tables now and easily stored with backups<br />
<br />
<br />
<div class="codebox">
<code><br />
mysql> desc mysql.slave_master_info; <br />
+------------------------+---------------------+------+-----+---------+-------+ <br />
| Field | Type | Null | Key | Default | Extra | <br />
+------------------------+---------------------+------+-----+---------+-------+ <br />
| Number_of_lines | int(10) unsigned | NO | | NULL | | <br />
| Master_log_name | text | NO | | NULL | | <br />
| Master_log_pos | bigint(20) unsigned | NO | | NULL | | <br />
| Host | char(64) | YES | | NULL | | <br />
| User_name | text | YES | | NULL | | <br />
| User_password | text | YES | | NULL | | <br />
| Port | int(10) unsigned | NO | | NULL | | <br />
| Connect_retry | int(10) unsigned | NO | | NULL | | <br />
| Enabled_ssl | tinyint(1) | NO | | NULL | | <br />
| Ssl_ca | text | YES | | NULL | | <br />
| Ssl_capath | text | YES | | NULL | | <br />
| Ssl_cert | text | YES | | NULL | | <br />
| Ssl_cipher | text | YES | | NULL | | <br />
| Ssl_key | text | YES | | NULL | | <br />
| Ssl_verify_server_cert | tinyint(1) | NO | | NULL | | <br />
| Heartbeat | float | NO | | NULL | | <br />
| Bind | text | YES | | NULL | | <br />
| Ignored_server_ids | text | YES | | NULL | | <br />
| Uuid | text | YES | | NULL | | <br />
| Retry_count | bigint(20) unsigned | NO | | NULL | | <br />
| Ssl_crl | text | YES | | NULL | | <br />
| Ssl_crlpath | text | YES | | NULL | | <br />
| Enabled_auto_position | tinyint(1) | NO | | NULL | | <br />
| Channel_name | char(64) | NO | PRI | NULL | | <br />
| Tls_version | text | YES | | NULL | | <br />
| Public_key_path | text | YES | | NULL | | <br />
| Get_public_key | tinyint(1) | NO | | NULL | | <br />
+------------------------+---------------------+------+-----+---------+-------+ <br />
27 rows in set (0.05 sec) <br />
<br />
mysql> desc mysql.slave_relay_log_info; <br />
+-------------------+---------------------+------+-----+---------+-------+ <br />
| Field | Type | Null | Key | Default | Extra | <br />
+-------------------+---------------------+------+-----+---------+-------+ <br />
| Number_of_lines | int(10) unsigned | NO | | NULL | | <br />
| Relay_log_name | text | NO | | NULL | | <br />
| Relay_log_pos | bigint(20) unsigned | NO | | NULL | | <br />
| Master_log_name | text | NO | | NULL | | <br />
| Master_log_pos | bigint(20) unsigned | NO | | NULL | | <br />
| Sql_delay | int(11) | NO | | NULL | | <br />
| Number_of_workers | int(10) unsigned | NO | | NULL | | <br />
| Id | int(10) unsigned | NO | | NULL | | <br />
| Channel_name | char(64) | NO | PRI | NULL | | <br />
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; min-height: 13.0px} span.s1 {font-variant-ligatures: no-common-ligatures} </style> <br />
+-------------------+---------------------+------+-----+---------+-------+ <br />
<br /> <br />
</code></div>
<span style="background-color: white;"><b>Turn off log_queries_not_using_indexes until needed </b></span><br />
<div>
<br />
<br />
<div>
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;">This was shown above also. This is a valid variable .. but depending on application it can load a slow log with useless info. Some tables might have 5 rows in it, you use it for some random drop down and you never put an index on it. With this enabled every time you query that table it gets logged. Now.. I am a big believer in you should put an index on it anyway. But focus this variable when you are looking to troubleshoot and optimize things. Let it run for at least 24hours so you get a full scope of a system if not a week.<br />
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><br /><br />mysql> SET GLOBAL log_queries_not_using_indexes=0; <br />Query OK, 0 rows affected (0.00 sec) <br />
</span></span><br />
<div class="p1">
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><br /></span></span> <span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;">To turn on </span></span><br />
<div class="codebox">
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><code><br />
mysql> SET GLOBAL log_queries_not_using_indexes=1; <br />
<br />
Query OK, 0 rows affected (0.00 sec) <br />
</code></span></span></div>
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><br /></span></span> <span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;">Note variable values into your my.cnf file to enable upon restart. </span></span><br />
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><br /></span></span> <span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><br /></span></span> <span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><b>Side note -- USE ALGORITHM=INPLACE </b></span></span><br />
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><br /></span></span>
<br />
<div>
<span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif; font-size: small; font-variant-ligatures: normal;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;">OK this is not a variable but more of a best practice. You should already be using EXPLAIN before you run a query, This shows you the query plan and lets you be sure all syntax is valid. I have seen more than once a Delete query executed without an WHERE by mistake. So 1st always use EXPLAIN to double check what you plan to do. Not the other process you should always do is try to use an </span></span><b style="font-family: Times, "Times New Roman", serif;">ALGORITHM=INPLACE or ALGORITHM=COPY when altering tables. </b></span></span></div>
</div>
</div>
</div>
<div>
<b style="font-family: Times, "Times New Roman", serif;"><span style="background-color: #e0e409; font-variant-ligatures: no-common-ligatures;"><br /></span></b></div>
<div>
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} span.s2 {font-variant-ligatures: no-common-ligatures; background-color: #e0e409} </style>
<br />
<div class="codebox">
<code><br />
mysql> ALTER TABLE TABLE_DEMO ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`); <br />
Query OK, 0 rows affected (1.49 sec) <br />
Records: 0 Duplicates: 0 Warnings: 0 <br />
</code></div>
<div>
<br />
<div>
<br />
<div>
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} span.s2 {font-variant-ligatures: no-common-ligatures; background-color: #e0e409} </style>
<br />
<div class="codebox">
<code><br />
mysql> ALTER TABLE TABLE_DEMO ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`); <br />
Query OK, 0 rows affected (1.49 sec) <br />
Records: 0 Duplicates: 0 Warnings: 0 <br />
</code></div>
<div>
<br />
<div>
<br />
<div>
<span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;">A list of online DLL operations is here<br />
</span></span><br />
<div>
<span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><a href="https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html">https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html</a> </span></span></div>
<span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"> </span></span><br />
<div>
<span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><br /><br />
</span></span></span></span><br />
<div>
<span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><br /><br />
<b><span style="font-family: "times" , "times new roman" , serif;">Side note -- USE mysql_config_editor</span></b></span></span></span></span></span></span></div>
<span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"> </span></span></span><br />
<div>
<span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;">Previous blog post about this is here </span></span></span></div>
<span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"> </span></span></span><br />
<div>
<span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><a href="https://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html">https://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html</a> </span></span></span></div>
<span style="background-color: white; font-variant-ligatures: no-common-ligatures;"><span style="font-family: "times" , "times new roman" , serif;"><span style="background-color: white; font-variant-ligatures: no-common-ligatures;">
<div>
<b><br /></b></div>
<div>
The simple example</div>
<div class="codebox">
<code><br />
mysql_config_editor set --login-path=local --host=localhost --user=root --password<br />Enter password:<br />
# mysql_config_editor print --all<br />
[local]<br />user = root<br />
password = *****<br />
host = localhost<br />
<br />
# mysql<br />
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)<br />
<br />
<br />
# mysql --login-path=local<br />
Welcome to the MySQL monitor. <br />
<br />
# mysql --login-path=local -e 'SELECT NOW()';<br />
</code></div>
<br />
<br />
<div>
<b>Side note -- USE mysql_upgrade --upgrade-system-tables</b></div>
<br />
<div>
Don't forget to use mysql_upgrade after you actually upgrade. </div>
<div>
This is often forgotten and leads to issues and errors at start up. You do not have to run upgrade across every table that exists though. The focus of most upgrade are the system tables. So at the very least focus on those. </div>
<br />
<div>
mysql_upgrade --login-path=local --upgrade-system-tables<br />
<br />
<div>
<br />
<br />
<style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} span.s2 {font-variant-ligatures: no-common-ligatures; background-color: #e0e409} </style><style type="text/css"> p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000} span.s1 {font-variant-ligatures: no-common-ligatures} </style></div>
</div>
</span></span></span></div>
<span style="background-color: white; font-variant-ligatures: no-common-ligatures;"> </span></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-9979895228307497842018-05-23T19:05:00.000-05:002018-05-23T19:05:58.650-05:00Proxy MySQL :: HAproxy || ProxySQL & KeepAlived<div dir="ltr" style="text-align: left;" trbidi="on">
So when it comes to routing your MySQL traffic several options exist.<br />
<br />
<ul style="text-align: left;">
<li><a href="http://www.haproxy.org/" target="_blank">HAproxy</a></li>
<li><a href="https://mariadb.com/products/technology/maxscale" target="_blank">MariaDB MaxScale</a></li>
<li><a href="https://www.nginx.com/" target="_blank">NGINX</a></li>
<li><a href="https://dev.mysql.com/doc/mysql-router/8.0/en/" target="_blank">MySQL Router</a></li>
<li><a href="http://www.proxysql.com/" target="_blank">ProxySQL</a></li>
<li>You can even still get <a href="https://launchpad.net/mysql-proxy" target="_blank">MySQL Proxy</a> if you wanted it but it is EOF. </li>
</ul>
<div>
Now I have seen HAproxy used more often with clients, it is pretty straight forward to set up. Percona has an example for those interested: </div>
<div>
<ul style="text-align: left;">
<li><a href="https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/haproxy.html">https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/haproxy.html</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
Personally I like ProxySQL. Percona also has few blogs on this as well</div>
<div>
<ul style="text-align: left;">
<li><a href="https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration">https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration</a></li>
<li><a href="https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/">https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/</a></li>
<li><a href="https://www.percona.com/blog/2017/01/25/proxysql-admin-configuration/">https://www.percona.com/blog/2017/01/25/proxysql-admin-configuration/</a></li>
<li><a href="https://www.percona.com/blog/2016/09/15/proxysql-percona-cluster-galera-integration/">https://www.percona.com/blog/2016/09/15/proxysql-percona-cluster-galera-integration/</a></li>
</ul>
</div>
<div>
Percona also has ProxySQL version available </div>
<div>
<ul style="text-align: left;">
<li><a href="https://www.percona.com/downloads/proxysql/">https://www.percona.com/downloads/proxysql/</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
I was thinking I would write up some examples but overall Percona has explained it all very well. I do not want to take anything away from those posts, instead point out that a lot of good information is available via those urls. So instead of rewriting what has already been written, I will create a collection of information for those interested. </div>
<div>
<br /></div>
<div>
First compare and decide for yourself what you need and want. The following link of course is going to be biased towards ProxySQL but it gives you an overall scope for you to consider. </div>
<div>
<ul style="text-align: left;">
<li><a href="http://www.proxysql.com/compare">http://www.proxysql.com/compare</a></li>
</ul>
<div>
If you have a cluster or master to master and you do not care which server the writes vs reads go onto, just as long as you have a connection; then HAproxy is likely a simple fast set up for you. </div>
</div>
<div>
<br /></div>
<div>
The bonus with ProxySQL is the ability to sort traffic in a weighted fashion, EASY. So you can have writes go to node 1, and selects pull from node 2 and node 3. Documentation on this can be found here:</div>
<div>
<ul style="text-align: left;">
<li><a href="https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO)">https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO)</a></li>
</ul>
<div>
Yes it can be done with HAproxy but you have to instruct the application accordingly. </div>
<div>
<ul style="text-align: left;">
<li><a href="https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial">https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial</a></li>
</ul>
</div>
<div>
This is handled in ProxySQL based on your query rules.</div>
<div>
<ul style="text-align: left;">
<li><a href="https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_query_rules">https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_query_rules</a></li>
<li><a href="https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#mysql-query-rules">https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#mysql-query-rules</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
Now the obvious question here: OK so how do you keep ProxySQL from becoming the single point of failure? </div>
</div>
<div>
<br /></div>
<div>
You can invest is a robust load balancer and etc etc etc ... Toss hardware at it.... Or make it easy on yourself and support open source and use <a href="http://www.keepalived.org/" target="_blank">KeepAlive</a>d. This is VERY easy to set up and all of it is documented again well here: </div>
<div>
<ul style="text-align: left;">
<li><a href="https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/">https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/</a></li>
<li><a href="http://www.keepalived.org/doc/">http://www.keepalived.org/doc/</a> </li>
<li>To be fair here is an example for keepalived and HAproxy as well </li>
<li> <a href="https://andyleonard.com/2011/02/01/haproxy-and-keepalived-example-configuration/">https://andyleonard.com/2011/02/01/haproxy-and-keepalived-example-configuration/</a></li>
</ul>
<div>
If you ever dealt with lua and <a href="https://anothermysqldba.blogspot.com/2013/05/setup-mysql-proxy.html" target="_blank">mysql-proxy</a>, ProxySQL and Keepalived should be very simple for you. If you still want it for some reason: <a href="https://launchpad.net/mysql-proxy">https://launchpad.net/mysql-proxy</a></div>
</div>
<div>
<br /></div>
<div>
Regardless if you choose HAproxy, ProxySQL or another solution, you need to ensure not to replace once single point of failure with another and keepalived is a great for that. So little reason to not do this if you are using a proxy. </div>
<div>
<br /></div>
<div>
So a few more things on ProxySQL. </div>
<div>
<ul style="text-align: left;">
<li>If you track hosts that connect to your database via your reporting or monitoring , realize those IPS or hostnames are now going to be the proxy server. </li>
<li>What about all the users you already have in MySQL then? Can you migrate them to proxysql? Yes you can. It takes a few steps but it is do able. Here is an example of this:</li>
<ul>
<li><a href="https://dba.stackexchange.com/questions/164705/how-to-easily-bring-80-mysql-users-into-proxysql">https://dba.stackexchange.com/questions/164705/how-to-easily-bring-80-mysql-users-into-proxysql</a></li>
</ul>
<li>Make sure you understand the <a href="https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL" target="_blank">Multi layer configuration system</a>. Save your info to disk!</li>
<ul>
<li><a href="https://github.com/sysown/proxysql/wiki/Main-(runtime)#runtime-tables">https://github.com/sysown/proxysql/wiki/Main-(runtime)#runtime-tables</a></li>
<li><a href="https://github.com/sysown/proxysql/wiki/Main-(runtime)#disk-database">https://github.com/sysown/proxysql/wiki/Main-(runtime)#disk-database</a></li>
</ul>
<li>Can ProxySQL run on the MySQL Default port 3306 </li>
<ul>
<li>Yes Edit the <a href="https://github.com/sysown/proxysql/wiki/Global-variables#mysql-interfaces" target="_blank">mysql-interfaces</a></li>
</ul>
<li>Keep in mind now your max_connections. If you have Max_connections in mysql set to 500, then that is your limit of course for standard users. With ProxySQL you can now spread users across the system and set a max per node. So to help ensure you do not hit 500 connections set the <a href="https://github.com/sysown/proxysql/wiki/Global-variables#mysql-max_connections" target="_blank">mysql-max_connections</a> a little bit lower than MySQL value. </li>
<li>Take advantage of the <a href="https://github.com/sysown/proxysql/wiki/Monitor-Module#monitor-module" target="_blank">Monitor Module</a> and <a href="https://github.com/sysown/proxysql/wiki/STATS-(statistics)#the-stats-database" target="_blank">STATS</a> .. Know what is going on with your proxy and traffic. </li>
<li>Take advantage of <a href="https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#query-caching" target="_blank">Query Caching</a> if applicable for your application.</li>
</ul>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<br /></div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-63313592490642594652018-05-23T07:32:00.002-05:002018-05-23T07:32:26.828-05:00Happy Birthday MySQL 1995<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white;">
<span style="font-family: Calibri, Helvetica, sans-serif, Helvetica, EmojiFont, Apple Color Emoji, Segoe UI Emoji, NotoColorEmoji, Segoe UI Symbol, Android Emoji, EmojiSymbols;">Happy Birthday MySQL ! Turned 23 today !</span></div>
<div style="background-color: white; font-family: Calibri, Helvetica, sans-serif, Helvetica, EmojiFont, "Apple Color Emoji", "Segoe UI Emoji", NotoColorEmoji, "Segoe UI Symbol", "Android Emoji", EmojiSymbols; font-size: 16px;">
<br /></div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-65814298544848216712018-03-20T01:26:00.000-05:002018-03-20T01:26:10.895-05:00MySQL 8.0.4rc<div dir="ltr" style="text-align: left;" trbidi="on">
MySQL 8.0.4rc was just released as "<span style="background-color: white; color: #ee0000; font-family: "open sans" , "arial" , "helvetica" , sans-serif; font-size: 12.1176px; font-weight: 700; text-align: center;">Pre-General Availability Draft: 2018-03-19</span>".<br />
<br />
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html">https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html</a></li>
<li><a href="https://dev.mysql.com/doc/relnotes/mysql/8.0/en/">https://dev.mysql.com/doc/relnotes/mysql/8.0/en/</a></li>
</ul>
<div>
I decided to take a quick peek and note my impressions here. Some of this is old news for many as this release has been talked about for awhile but I added my thoughts anyway.. </div>
<div>
<br /></div>
<div>
First thing I noticed was a simple issue of using the updated mysql client. My older version was still in my path that resulted in </div>
<div class="codebox">
<code>
</code>
<br />
<div>
<code>ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded</code></div>
<code>
</code></div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html">https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html</a></li>
</ul>
</div>
<div>
So simple fix and make sure you are using the valid updated mysql client. Of course other options existed like changing the authentication plugin back to mysql_native_password but why bother, use the secure method. This is a very good enhancement for security so do not be shocked if you have some connection issues while you get your connections using this more secure method. </div>
<div>
<br /></div>
<div class="codebox">
<code>
</code>
<br />
<div>
<div>
<code>Welcome to the MySQL monitor. Commands end with ; or \g.</code></div>
<div>
<code>Your MySQL connection id is 36</code></div>
<div>
<code>Server version: 8.0.4-rc-log</code></div>
<div>
<code><br /></code></div>
<div>
<code>Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.</code></div>
</div>
<code>
</code></div>
<div>
<br /></div>
<div>
So the first very cool enhancement... </div>
<div>
<div class="codebox">
<code>
</code>
<br />
<div>
<code>mysql> show create table user\G</code></div>
<code>
</code>
<div>
<code>*************************** 1. row ***************************</code></div>
<code>
<div>
Table: user</div>
<div>
Create Table: CREATE TABLE `user` (</div>
<div>
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',</div>
<div>
`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',</div>
<div>
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',</div>
<div>
`ssl_cipher` blob NOT NULL,</div>
<div>
`x509_issuer` blob NOT NULL,</div>
<div>
`x509_subject` blob NOT NULL,</div>
<div>
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',</div>
<div>
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',</div>
<div>
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',</div>
<div>
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',</div>
<div>
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'caching_sha2_password',</div>
<div>
`authentication_string` text COLLATE utf8_bin,</div>
<div>
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`password_last_changed` timestamp NULL DEFAULT NULL,</div>
<div>
`password_lifetime` smallint(5) unsigned DEFAULT NULL,</div>
<div>
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Create_role_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Drop_role_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',</div>
<div>
`Password_reuse_history` smallint(5) unsigned DEFAULT NULL,</div>
<div>
`Password_reuse_time` smallint(5) unsigned DEFAULT NULL,</div>
<div>
PRIMARY KEY (`Host`,`User`)</div>
<div>
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Users and global privileges'</div>
<div>
1 row in set (0.00 sec)</div>
</code></div>
</div>
<div>
<br /></div>
<div>
Yep user table is InnoDB and has own TableSpace. </div>
<div>
<br /></div>
<div>
With the addition of the new Data Dictionary you will now notice Information_schema changes. </div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-information-schema.html">https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-information-schema.html</a></li>
</ul>
<div>
So as a simple example the Columns table historically has not been a view but that has now changed , along with many others as you can see via the url provided. </div>
</div>
<div>
<br /></div>
<div>
<div class="codebox">
<code>
</code>
<br />
<div>
<code>mysql> show create table COLUMNS \G</code></div>
<code>
</code>
<div>
<code>*************************** 1. row ***************************</code></div>
<code>
<div>
View: COLUMNS</div>
<div>
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` </div>
</code></div>
</div>
<div>
<br /></div>
<div>
This appears to be done to help performance with the information_schema but removing the temporary table creations per queries into the information_schema. </div>
<div>
<br /></div>
<div>
Chapter 14 of the documentation goes into depth on this, the provided url below will help you find more information and future blog posts might touch more on this. </div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html">https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html</a></li>
</ul>
<div>
The previously mentioned Data Dictionary then also leads into the ability to have atomic Data Definition Language (DDL) statements or atomic DDL. </div>
</div>
<div>
<br /></div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html">https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
This is likely to trip up a few transactions if you do not review your queries before setting up replication to a new MySQL 8.0 instance. I say that because of how the handling of table maintenance could be impacted. If you write clean queries with "If Exists" it won't be a big problem. Overall it is a more transaction based feature that protects your data and rollback options. </div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Resource management looks very interesting and I will have to take more time to focus on this as it is a new feature with MySQL 8.0. Overall you can assign groups and no longer have to set priority of query but let your grouping define how a query should behave and resources allotted to it. </div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html">https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html</a></li>
</ul>
</div>
<div>
<div class="codebox">
<code>
</code>
<br />
<div>
<code>mysql> select @@version;</code></div>
<code>
</code>
<div>
<code>+------------+</code></div>
<code>
<div>
| @@version |</div>
<div>
+------------+</div>
<div>
| 5.7.16-log |</div>
<div>
+------------+</div>
<div>
1 row in set (0.00 sec)</div>
<div>
<br /></div>
<div>
mysql> desc INFORMATION_SCHEMA.RESOURCE_GROUPS;</div>
<div>
ERROR 1109 (42S02): Unknown table 'RESOURCE_GROUPS' in information_schema</div>
<div>
<br /></div>
<div>
mysql> select @@version;</div>
<div>
+--------------+</div>
<div>
| @@version |</div>
<div>
+--------------+</div>
<div>
| 8.0.4-rc-log |</div>
<div>
+--------------+</div>
<div>
1 row in set (0.00 sec)</div>
<div>
<br /></div>
<div>
mysql> desc INFORMATION_SCHEMA.RESOURCE_GROUPS;</div>
<div>
+------------------------+-----------------------+------+-----+---------+-------+</div>
<div>
| Field | Type | Null | Key | Default | Extra |</div>
<div>
+------------------------+-----------------------+------+-----+---------+-------+</div>
<div>
| RESOURCE_GROUP_NAME | varchar(64) | NO | | NULL | |</div>
<div>
| RESOURCE_GROUP_TYPE | enum('SYSTEM','USER') | NO | | NULL | |</div>
<div>
| RESOURCE_GROUP_ENABLED | tinyint(1) | NO | | NULL | |</div>
<div>
| VCPU_IDS | blob | YES | | NULL | |</div>
<div>
| THREAD_PRIORITY | int(11) | NO | | NULL | |</div>
<div>
+------------------------+-----------------------+------+-----+---------+-------+</div>
<div>
5 rows in set (0.00 sec)</div>
<div>
<br /></div>
</code></div>
</div>
<div>
<br /></div>
<div>
More insight into your InnoDB buffer pool cache in regards to the indexes that are in it is now available. </div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-cached-indexes-table.html">https://dev.mysql.com/doc/refman/8.0/en/innodb-cached-indexes-table.html</a></li>
</ul>
</div>
<div>
<div class="codebox">
<code>
</code>
<br />
<div>
<code>mysql> desc INFORMATION_SCHEMA.INNODB_CACHED_INDEXES ;</code></div>
<code>
</code>
<div>
<code>+----------------+---------------------+------+-----+---------+-------+</code></div>
<code>
<div>
| Field | Type | Null | Key | Default | Extra |</div>
<div>
+----------------+---------------------+------+-----+---------+-------+</div>
<div>
| SPACE_ID | int(11) unsigned | NO | | | |</div>
<div>
| INDEX_ID | bigint(21) unsigned | NO | | | |</div>
<div>
| N_CACHED_PAGES | bigint(21) unsigned | NO | | | |</div>
<div>
+----------------+---------------------+------+-----+---------+-------+</div>
<div>
3 rows in set (0.01 sec)</div>
</code></div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
If you are unsure what to set the InnoDB Buffer pool , log_sizes or flush method MySQL will set these for you now based on the available memory. </div>
<div>
<br /></div>
<div>
<div>
innodb_dedicated_server</div>
<div class="codebox">
<code>
</code>
<br />
<div>
<code>[mysqld]</code></div>
<code>
</code>
<div>
<code>innodb-dedicated-server=1</code></div>
<code>
<div>
<br /></div>
<div>
mysql> select @@innodb_dedicated_server;</div>
<div>
+---------------------------+</div>
<div>
| @@innodb_dedicated_server |</div>
<div>
+---------------------------+</div>
<div>
| 1 |</div>
<div>
+---------------------------+</div>
</code></div>
</div>
<div>
<br /></div>
<div>
This simple test set my innodb_buffer_pool_size to 6GB for example when the default is 128MB. </div>
<div>
<br /></div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html">https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html</a></li>
</ul>
</div>
<div>
Numerous JSON additions have been made as well as regular expression changes. Both of which look promising. </div>
<div>
<br /></div>
<div>
The only replication enhancement per this release itself is that is now supports binary logging of partial updates to JSON documents using a compact binary format. </div>
<div>
<br /></div>
<div>
However overall many features are available ( you can read all about them <a href="http://mysqlhighavailability.com/replication-features-in-mysql-8-0-1/" target="_blank">here</a>) , one of which (I wish my client had tomorrow ) is replication filers per channel. </div>
<div>
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/8.0/en/change-replication-filter.html">https://dev.mysql.com/doc/refman/8.0/en/change-replication-filter.html</a></li>
</ul>
</div>
<div>
My test instance already had binary logs enabled, but they are on by default now along with TABLE based versus file based master & slave info, ( big fan of having that transaction based by default )</div>
<div>
<br /></div>
<div>
Overall keep in mind this is just a first glance at this release and very high level thoughts on it, many other changes exist. Looking over other <a href="https://planet.mysql.com/" target="_blank">blog posts</a> about this release as well as the <a href="https://dev.mysql.com/doc/relnotes/mysql/8.0/en/" target="_blank">manual</a> and <a href="https://dev.mysql.com/doc/relnotes/mysql/8.0/en/" target="_blank">release notes </a>will also help. Certainly download and review as it looks to be very promising for administration, security and replication points of view. </div>
<div>
<br /></div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-76225786931381453262018-03-13T18:40:00.000-05:002018-03-13T18:40:13.801-05:00MySQL Cheat Sheet<div dir="ltr" style="text-align: left;" trbidi="on">
So first I have posted in sometime as felt I should be. I have been very busy still working with MySQL and all related forks and failed to put out blogs as I felt I should. So I will work on that.
<br />
<br />
Now That being said I recalled the other day a website I used to love because it was a common VI cheat sheet list. The syntax you know , you know you need it, but type it 3 times until it right. When it does get entered right you look at it dumbfounded , I thought I wrote that already.
<br />
<br />
So I figured why not a simple list of common MySQL commands that we all either type 50 times a month or should know like the back of our hand but forget when the client is looking over our shoulder.<br />
For starters..<br />
We set up a new MySQL 5.7.6+ server and log in..<br />
Need to change password before we can do anything. But it is Alter user not Set pass.<br />
We want to know how to read the password still as it is in clear text.<br />
<br />
ALTER USER <br />
<div class="codebox">
<code>
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
<br />
</code></div>
Set Password is <br />
<div class="codebox">
<code>
SET PASSWORD FOR 'bob''@'localhost' = PASSWORD('cleartext password');
<br />
</code></div>
<br />
Purge Binary Logs <br />
<div class="codebox">
<code>
PURGE BINARY LOGS TO 'mysql-bin.010';<br />
PURGE BINARY LOGS BEFORE '2008-04-02 00:00:00<br />
PURGE BINARY LOGS BEFORE NOW() - interval 3 DAY;<br />
<br />
</code></div>
<br />
MySQL Dump
<br />
<div class="codebox">
<code>
# COMPACT WILL REMOVE DROP STATEMENTS<br />
mysqldump --events --master-data=2 --routines --triggers --compact --all-databases > db.sql<br />
mysqldump --events --master-data=2 --routines --triggers --all-databases > NAME.sql <br />
mysqldump --opt --routines --triggers dbname > dbname.sql <br />
mysqldump --opt --routines --triggers --no-create-info joomla jforms > dataonly.sql <br />
</code></div>
<br />
Turn off Foreign Keys for a moment
<br />
<div class="codebox">
<code>
SET GLOBAL foreign_key_checks=0;
<br />
</code></div>
<br />
<br />
<br />
Skip Grants<br />
<div class="codebox">
<code>
/usr/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf --skip-grant-tables<br />
vi /etc/mysql/my.cnf<br />
[mysqld]<br />
skip-grant-tables<br />
<br />
</code></div>
<br />
<br />
BinLog reviews<br />
--base64-output=DECODE-ROWS & --verbose<br />
<div class="codebox">
<code>
mysqlbinlog --defaults-file=/home/anothermysqldba/.my.cnf --base64-output=DECODE-ROWS --verbose binlog.005862 > 005862.sql<br />
<br />
</code></div>
<br />
MYSQL SECURE CLIENT<br />
<div class="codebox">
<code>
mysql_config_editor print --all<br />
mysql_config_editor set --user=mysql --password --login-path=localhost --host=localhost<br />
mysql --login-path=localhost -e 'SELECT NOW()';<br />
<br />
</code></div>
<br />
Swap
<br />
<div class="codebox">
<code>
sudo swapoff -a<br />
To set the new value to 10: echo 10 | sudo tee /proc/sys/vm/swappiness<br />
sudo swapon -a
<br />
</code></div>
<br />
IF INFORMATION SCHEMA IS SLOW<br />
<div class="codebox">
<code>
set global innodb_stats_on_metadata=0;
<br />
</code></div>
<br />
AWS Variables <br />
<div class="codebox">
<code>
CALL mysql.rds_show_configuration;<br />
> call mysql.rds_set_configuration('binlog retention hours', 24);<br />
> call mysql.rds_set_configuration('slow_launch_time', 2);<br />
<br />
</code></div>
<br />
Find what table a column name is in<br />
<div class="codebox">
<code>
SELECT TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'fieldname' ;
<br />
</code></div>
Client says it is in TableA but they have 50 databases.. What schema has TableA<br />
<div class="codebox">
<code>
SELECT TABLE_SCHEMA , TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME = 'TableA' ;
<br />
</code></div>
<br />
Adjust Slave workers<br />
<div class="codebox">
<code>
Select @@slave_parallel_workers;<br />
Stop Slave; Set GLOBAL slave_parallel_workers=5; Start Slave;<br />
<br />
</code></div>
<br />
MySQL Multi
<br />
<div class="codebox">
<code>
5.6><br />
To start both : mysqld_multi start 1,2 <br />
To check on status of both: mysqld_multi report 1,2<br />
To check on status or other options you can use just one <br />
<br />
5.7<<br />
[mysqld1] BECOMES [mysqld@mysqld1] <br />
systemctl start mysqld@mysqld1<br />
systemctl start mysqld@mysqld2<br />
systemctl start mysqld@mysqld3<br />
systemctl start mysqld@mysqld4<br />
</code></div>
MySQL Upgrade System tables only
<br />
<div class="codebox">
<code>
mysql_upgrade --defaults-file=/home/<span style="background-color: white;">anothermysqldba</span>/.my.cnf --upgrade-system-tables
<br />
</code></div>
<br />
SKIP REPLICATION ERROR<br />
<div class="codebox">
<code>
STOP SLAVE; SET GLOBAL sql_slave_skip_counter =1; START SLAVE; SELECT SLEEP(1); SHOW SLAVE STATUS\G
<br />
</code></div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-87210277511571546802017-10-13T18:42:00.000-05:002018-03-13T17:08:16.376-05:00MySQL InnoDB Tablespace Encryption<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
MySQL Tablespace Encryption is relatively simple to set up. You have to be using MySQL 5.7, innodb_file_per_table<br />
<br />
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html">https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html </a></li>
</ul>
<br />
The MySQL documentation covers everything you need to set this up:<br />
<br />
<ul style="text-align: left;">
<li><a href="https://dev.mysql.com/doc/refman/5.7/en/keyring.html">https://dev.mysql.com/doc/refman/5.7/en/keyring.html</a></li>
<li><a href="https://www.percona.com/blog/2016/04/08/mysql-data-at-rest-encryption">https://www.percona.com/blog/2016/04/08/mysql-data-at-rest-encryption</a></li>
<li><a href="http://mysqlserverteam.com/mysql-enterprise-transparent-data-encryption-tde-provides-at-rest-encryption-for-physical-innodb-tablespace-data-files/">http://mysqlserverteam.com/mysql-enterprise-transparent-data-encryption-tde-provides-at-rest-encryption-for-physical-innodb-tablespace-data-files/</a></li>
</ul>
<br />
But my blog post would be very short if I just shared a link. So....<br />
<br />
Once upgraded and on MySQL 5.7 you need to have a valid location for your key file. I have a MySQL datadir as /var/lib/mysql/data this allows default locations for people to find it if needed but also allows a simple mounted location for more disks if needed as well. This also avoids having to do additional edits to selinux and etc since it already allows /var/lib/mysql.<br />
<br />
<div class="codebox">
<code>
chmod 750 /var/lib/mysql/keyring*<br />
chown mysql:mysql /var/lib/mysql/keyring<br />
<br />
touch /var/lib/mysql/keyring<br />
Edit the mysql .cnf file :<br />
<br />
ls /usr/lib64/mysql/plugin/keyring_file.so<br />
/usr/lib64/mysql/plugin/keyring_file.so<br />
<br />
[mysqld]<br />
# Keyring Plugin Installation<br />
early-plugin-load=keyring_file.so<br />
keyring_file_data=/var/lib/mysql/keyring<br />
<br />
</code></div>
After MySQL startup check your plugins:<br />
<br />
<div class="codebox">
<code>
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS<br />
-> FROM INFORMATION_SCHEMA.PLUGINS<br />
-> WHERE PLUGIN_NAME LIKE 'keyring%';<br />
+--------------+---------------+<br />
| PLUGIN_NAME | PLUGIN_STATUS |<br />
+--------------+---------------+<br />
| keyring_file | ACTIVE | <br />
| keyring_udf | ACTIVE |<br />
+--------------+---------------+<br />
</code></div>
<div>
<br /></div>
<br />
<div class="codebox">
<code>
INSTALL PLUGIN keyring_udf SONAME 'keyring_udf.so';<br />
CREATE FUNCTION keyring_key_generate RETURNS INTEGER SONAME 'keyring_udf.so';<br />
CREATE FUNCTION keyring_key_fetch RETURNS STRING SONAME 'keyring_udf.so';<br />
CREATE FUNCTION keyring_key_length_fetch RETURNS INTEGER SONAME 'keyring_udf.so';<br />
CREATE FUNCTION keyring_key_type_fetch RETURNS STRING SONAME 'keyring_udf.so';<br />
CREATE FUNCTION keyring_key_store RETURNS INTEGER SONAME 'keyring_udf.so';<br />
CREATE FUNCTION keyring_key_remove RETURNS INTEGER SONAME 'keyring_udf.so';<br />
<br />
<br />
mysql> use test;<br />
mysql> SELECT keyring_key_generate('MyKey1', 'AES', 32);<br />
<br />
mysql> CREATE TABLE `enc_test` (<br />
`enc_test_id` varchar(255) NOT NULL,<br />
PRIMARY KEY (`enc_test_id`)<br />
) ENGINE=InnoDB ;<br />
<br />
mysql> alter table test.enc_test encryption='Y';<br />
<br />
mysql> show create table enc_test \G<br />
*************************** 1. row ***************************<br />
Table: enc_test<br />
Create Table: CREATE TABLE `enc_test` (<br />
`enc_test_id` varchar(255) NOT NULL,<br />
PRIMARY KEY (`enc_test_id`)<br />
) ENGINE=InnoDB DEFAULT CHARSET=latin1 <b>ENCRYPTION='Y'</b><br />
1 row in set (0.00 sec)<br />
<br />
</code></div>
<div>
<br /></div>
OK great you have a table.<br />
What about backups.<br />
<br />
Well from my current experience innobackupex does not work, but you can still use xtrabackup.<br />
<br />
<div class="codebox">
<code>
xtrabackup --defaults-file=/home/anothermysqldba/.my.cnf --port=3306 --socket=/tmp/mysql.sock --backup --keyring-file-data=/var/lib/mysql/keyring --server_id=1 --target-dir=/backups/database/ ; xtrabackup --prepare --keyring-file-data=/var/lib/mysql/keyring --target-dir=/backups/database/
<br />
</code></div>
</div>
<br />
<div>
<br /></div>
<br />
<br />
<br /></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-68638766245598713932016-04-22T09:58:00.000-05:002016-04-22T09:58:39.572-05:00Math with MySQL<div dir="ltr" style="text-align: left;" trbidi="on">
I thought I posted this long ago... oh well....<br />
<br />
We all know that math is the fundamental aspect of all life and the common language used around the world if not beyond. MySQL, like all databases, can help you with numerous aspects of math.<br />
<br />
Here is a list of the functions: <a href="https://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html">https://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html</a><br />
<br />
Here are some simple examples to get you started. <br />
<ul style="text-align: left;">
<li>The Quadratic Formula ax^2 + bx + c = 0</li>
</ul>
<div class="codebox">
<code><br />
# 2x^2 – 4x – 3 = 0.<br />
SET @a= 1;<br />
SET @b= 3;<br />
SET @c= -4;<br />
SET @XX = ( -(@b) - SQRT( POW(@b,2) -4 * @a * @c) / POW(@a,2) ) ; <br />
SET @YY = ( -(@b) + SQRT( POW(@b,2) -4 * @a * @c) / POW(@a,2) ) ; <br />
SET @XXX = MOD(@YY, @XX);<br />
<br />
SELECT @XX / @XXX as X; <br />
+------+<br />
| X |<br />
+------+<br />
| -4 |<br />
+------+<br />
SELECT @YY / @XXX as X ; <br />
+------+<br />
| X |<br />
+------+<br />
| 1 |<br />
+------+<br />
<br />
</code></div>
<br />
<ul style="text-align: left;">
<li>The Pythagorean Theorem (remember geometry 101): A^2 + B^2 = C^2</li>
</ul>
<div class="codebox">
<code><br />
SET @A = 14;<br />
SET @B = 48;<br />
SELECT @C := SQRT(POW(@A,2) + POW(@B,2) );<br />
+-------------------------------------+<br />
| @C := SQRT(POW(@A,2) + POW(@B,2) ) |<br />
+-------------------------------------+<br />
| 50 |<br />
+-------------------------------------+<br />
</code></div>
<br />
<br />
So that solves C and of course you use this to solve for A as well. <br />
<div class="codebox">
<code><br />
SELECT @A := SQRT(POW(@C,2) - POW(@B,2)) ; <br />
+-----------------------------------+<br />
| @A := SQRT(POW(@C,2) - POW(@B,2)) |<br />
+-----------------------------------+<br />
| 14 |<br />
+-----------------------------------+ <br />
</code></div>
<br />
<br />
<ul style="text-align: left;">
<li>The logarithm and its identities log xy = log x + log y </li>
</ul>
<a href="http://www.businessinsider.com/the-17-equations-that-changed-the-world-2012-7#the-logarithm-and-its-identities-2">http://www.businessinsider.com/the-17-equations-that-changed-the-world-2012-7#the-logarithm-and-its-identities-2</a><br />
<br />
<div class="codebox">
<code><br />
SET @X = 2;<br />
SET @Y = 3;<br />
SELECT concat(log(@X * @Y) ,' = ', log(@X) + log(@Y) ) as "logarithm and its identities" ; <br />
+---------------------------------------+<br />
| logarithm and its identities |<br />
+---------------------------------------+<br />
| 1.791759469228055 = 1.791759469228055 |<br />
+---------------------------------------+<br />
</code></div>
<br />
<ul style="text-align: left;">
<li>Euler's formula for polyhedra : F - E + V = 2 </li>
</ul>
<a href="http://www.businessinsider.com/the-17-equations-that-changed-the-world-2012-7#eulers-formula-for-polyhedra-6">http://www.businessinsider.com/the-17-equations-that-changed-the-world-2012-7#eulers-formula-for-polyhedra-6</a><br />
<div class="codebox">
<code> <br />
SET @V = 4; # Vertices<br />
SET @E = 6; # Edges<br />
SET @F = 4; # Faces<br />
SELECT @V - @E + @F as Tetrahedron; <br />
<br />
SET @V = 8; # Vertices<br />
SET @E = 12; # Edges<br />
SET @F = 6; # Faces<br />
SELECT @V - @E + @F as Hexahedron; <br />
<br />
SET @V = 12; # Vertices<br />
SET @E = 30; # Edges<br />
SET @F = 20; # Faces<br />
SELECT @V - @E + @F as Icosahedron; <br />
<br />
SET @V = 12; # Vertices<br />
SET @E = 30; # Edges<br />
SET @F = 20; # Faces<br />
SELECT @V - @E + @F as Icosahedron;<br />
</code></div>
<br />
<ul style="text-align: left;">
<li>Einstein's theory of relativity E = mc^2 </li>
</ul>
<div class="codebox">
<code><br />
SET @lbs = 190; # lbs <br />
SET @lb2gram = 453.6; # 1 lbs = 453.6g <br />
SET @lbstograms := @lbs * @lb2gram / 1;<br />
SET @m := @lbstograms * 1 / 1000;<br />
SET @c := POW(3.00 * POW(10,8), 2 );<br />
SELECT @E := @m * @c ;<br />
+----------------+<br />
| @E := @m * @c |<br />
+----------------+<br />
| 7.75656e18 |<br />
+----------------+ <br />
</code></div>
<br />
<ul style="text-align: left;">
<li>1 = 0.9999.....</li>
</ul>
<div class="codebox">
<code><br />
SELECT SUM(.9/(9/10));<br />
+----------------+<br />
| SUM(.9/(9/10)) |<br />
+----------------+<br />
| 1.00000 |<br />
+----------------+<br />
</code></div>
<br />
<br />
<br />
<br /></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-89892802707483776542015-07-25T11:16:00.000-05:002015-07-25T11:16:14.525-05:00Quick Password generation with MySQL <div dir="ltr" style="text-align: left;" trbidi="on">
Here is a quick and simple way to generate a password your application using MySQL.<br />
This query will create a upper and lower case randomly generated password in length and values.<br />
<br />
<div class="codebox">
<code> <br />
SELECT CONCAT(UCASE(SUBSTRING( SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , FLOOR( 1 + (RAND() * 4 )) , FLOOR( 4 + (RAND() * 12 )) )), <br />SUBSTRING('!@#$%^&*', FLOOR( 1 + (RAND() * 4)) , FLOOR( 1 + (RAND() * 8)) ),<br />SUBSTRING( SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , 5)) as password \G <br />
</code></div>
<br />
<br />
This query will take a random value for the 1st part of the dynamic password and upper case it when applicable then the second half it adds some symbols then the remaining is in lower case because MD5 does that automatically. You of course can adjust whatever symbols you would prefer.<br />
<br />
Example of results:<br />
<div class="codebox">
<code> <br />
CREATE TABLE `generated` (<br />
`password` varchar(255) NOT NULL<br />
) ENGINE=InnoDB ;<br />
<br />
</code><code><code><code>MariaDB [(none)]> </code><code>INSERT INTO test.generated SELECT CONCAT(UCASE(SUBSTRING( SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , FLOOR( 1 + (RAND() * 4 )) , FLOOR( 4 + (RAND() * 12 )) )), SUBSTRING('!@#$%^&*', FLOOR( 1 + (RAND() * 4)) , FLOOR( 1 + (RAND() * 8)) ),SUBSTRING( SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , 5)) as password; </code></code> <br />
<br />
MariaDB [(none)]> select * from test.generated; <br />
+-----------------------------------+<br />| password |<br />+-----------------------------------+<br />| 7232E1D9$%^&*f7de22c1b14f15c8a6 |<br />| DEE22B7F7#$%541adcdd1b8e2 |<br />| 2C4BB81001@#$%^&d0d9a2126 |<br />| 941D3B92D@#$%^a4b7be92 |<br />| 1CF2563254FC@#a79bb5 |<br />| 2AE86E7D6!@#$%^&*4ca03f2e8 |<br />| 074DD8D3#$e70a1 |<br />| B2DDC!@#$%^47a252f79 |<br />| 3A6D0A#$%^&*01ce9278a2a |<br />| E9FB4CD16E19!739db9faa1616505c |<br />| 1ED7A2E1379B4!@#$%ebe60b |<br />| E9B57D71DB1@#$%^e9a4f8c2e94bf3d35 |<br />| C97982!@#$8b5c534653c06 |<br />| DE8998057C8!@#$%^8977a807 |<br />| 7C7172EB1AE5$%^&*79d2b27 |<br />| B52CE71@#$%^&*4508 |<br />| AB17714F!@#$%^&203ba4ff80f8a6 |<br />| 558D@#$04087871e29ff54 |<br />| 27A78E8EF#$%^b8cee8628d81593 |<br />| 315F2EC4#ad9913ec0595c |<br />+-----------------------------------+</code><br />
<code><br />
</code></div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-799200779622291402015-06-28T13:21:00.000-05:002015-06-28T13:21:39.182-05:00Fibonacci Sequence Procedure<div dir="ltr" style="text-align: left;" trbidi="on">
Well it has been awhile since I posted, sorry about that. Today I was watching reruns of the TV show Fringe and when Walter referenced the Fibonacci Sequence I got side tracked with MySQL options for this.<br />
<br />
Now this post already existed:<br />
<ul style="text-align: left;">
<li><a href="http://slava-technical.blogspot.com/2010/10/fibonacci-numbers-built-with-sql.html">http://slava-technical.blogspot.com/2010/10/fibonacci-numbers-built-with-sql.html</a></li>
</ul>
<br />
So I took that post and expanded on it a little, the result is a procedure that you can call and return the range within the Fibonacci Sequence that you are after.<br />
<br />
The procedure is below:<br />
<div class="codebox">
<code><br />
delimiter //<br />
CREATE PROCEDURE `Fibonacci`(IN POS INT, IN RANG INT, IN LIMTED INT)<br />
BEGIN<br />
select FORMAT(Fibonacci,0) AS Fibonacci from (<br />
select @f0 Fibonacci, @fn:=@f1+@f0, @f0:=@f1, @f1:=@fn <br />
from (select @f0:=0, @f1:=1, @fn:=1) x, <br />
information_schema.STATISTICS p limit LIMTED) y LIMIT POS, RANG;<br />
END//<br />
delimiter ;<br />
</code></div>
<br />
<br />
You can call this and pass whatever values and ranges you are after.<br />
<br />
So if you want the 5th value (<i>starting from 0</i>) in the sequence and the next value<br />
<div class="codebox">
<code><br />
> CALL Fibonacci(5,2,100);<br />
+-----------+<br />
| Fibonacci |<br />
+-----------+<br />
| 5 |<br />
| 8 |<br />
+-----------+ <br />
</code></div>
<br />
<br />
So if you want the 30th value (<i>starting from 0</i>) in the sequence and the next value<br />
<div class="codebox">
<code><br />
> CALL Fibonacci(30,2,100);<br />
+-----------+<br />
| Fibonacci |<br />
+-----------+<br />
| 832,040 |<br />
| 1,346,269 |<br />
+-----------+<br />
</code></div>
<br />
<br />
So if you want the 150th value (<i>starting from 0</i>) in the sequence and the next value<br />
<div class="codebox">
<code><br />
> CALL Fibonacci(150,2,1000);<br />
+--------------------------------------------+<br />
| Fibonacci |<br />
+--------------------------------------------+<br />
| 9,969,216,677,189,305,000,000,000,000,000 |<br />
| 16,130,531,424,904,583,000,000,000,000,000 |<br />
+--------------------------------------------+<br />
</code></div>
<br />
<br />
So you get the idea. Now you can also expand the range of results if you want more than 2 just change the 2nd value in the procedure call.<br />
<div class="codebox">
<code><br />
> CALL Fibonacci(0,10,100);<br />
+-----------+<br />
| Fibonacci |<br />
+-----------+<br />
| 0 |<br />
| 1 |<br />
| 1 |<br />
| 2 |<br />
| 3 |<br />
| 5 |<br />
| 8 |<br />
| 13 |<br />
| 21 |<br />
| 34 |<br />
+-----------+<br />
<br />
> CALL Fibonacci(30,5,100);<br />
+-----------+<br />
| Fibonacci |<br />
+-----------+<br />
| 832,040 |<br />
| 1,346,269 |<br />
| 2,178,309 |<br />
| 3,524,578 |<br />
| 5,702,887 |<br />
+-----------+ <br />
</code></div>
<br />
<br />
Anyway, hope someone finds it helpful and credit for the base of query does go to the original post. </div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-40254379181731134622014-12-26T12:14:00.000-06:002014-12-26T12:14:00.449-06:00Find/parse a string from within a string<div dir="ltr" style="text-align: left;" trbidi="on">
So I noticed a few different questions and posts about parsing a string out of another string recently. While some solutions included creating new functions and etc it can also be done within a single query in some cases.<br />
<br />
For example, let us say that we are looking to pull out the domain from a URL. I will try to go into detail as to why and how this works.<br />
We have the following table. <br />
<div class="codebox">
<code><br />
CREATE TABLE `parse_example` (<br />
`id` int(11) NOT NULL AUTO_INCREMENT,<br />
`urldemo` varchar(150) NOT NULL,<br />
PRIMARY KEY (`id`)<br />
) ENGINE=InnoDB;<br />
+----+----------------------------+<br />
| id | urldemo |<br />
+----+----------------------------+<br />
| 1 | http://www.mysql.com/ |<br />
| 2 | http://www.percona.com/ |<br />
| 3 | https://tools.percona.com/ |<br />
| 4 | https://mariadb.com/ |<br />
| 5 | http://planet.mysql.com/ |<br />
| 6 | http://dev.mysql.com/doc/ |<br />
+----+----------------------------+<br />
</code></div>
<br />
<br />
The goal for this example is to disregard the http:// or https:// and anything after the .com. So we use LOCATE to find the locations. <br />
<br />
The .com reference is easy since that is constant so we can start with that. <br />
<div class="codebox">
<code><br />
SELECT LOCATE('.com', urldemo), urldemo FROM parse_example;<br />
+-------------------------+----------------------------+<br />
| LOCATE('.com', urldemo) | urldemo |<br />
+-------------------------+----------------------------+<br />
| 17 | http://www.mysql.com/ |<br />
| 19 | http://www.percona.com/ |<br />
| 22 | https://tools.percona.com/ |<br />
| 16 | https://mariadb.com/ |<br />
| 20 | http://planet.mysql.com/ |<br />
| 17 | http://dev.mysql.com/doc/ |<br />
+-------------------------+----------------------------+<br />
</code></div>
<br />
<br />
OK so we want to remove the / , what location is that?<br />
<div class="codebox">
<code><br />
SELECT LOCATE('.com', urldemo) as start, LOCATE('.com', urldemo) +4 as end, SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 ) AS resulting , urldemo FROM parse_example;<br />
+-------+-----+-----------+----------------------------+<br />
| start | end | resulting | urldemo |<br />
+-------+-----+-----------+----------------------------+<br />
| 17 | 21 | / | http://www.mysql.com/ |<br />
| 19 | 23 | / | http://www.percona.com/ |<br />
| 22 | 26 | / | https://tools.percona.com/ |<br />
| 16 | 20 | / | https://mariadb.com/ |<br />
| 20 | 24 | / | http://planet.mysql.com/ |<br />
| 17 | 21 | /doc/ | http://dev.mysql.com/doc/ |<br />
+-------+-----+-----------+----------------------------+</code></div>
<br />
This gives us our end position, I only put the field aliases in to make the results easier to follow.<br />
<br />
Now sorting out after http and https is actually very easy as well as they both have :// after them, so we just need the location of the second / in the string.<br />
<br />
<div class="codebox">
<code><br />
SELECT LOCATE('/', urldemo) as first, LOCATE('/', urldemo) +1 as second, urldemo<br />
FROM parse_example;<br />
+-------+--------+----------------------------+<br />
| first | second | urldemo |<br />
+-------+--------+----------------------------+<br />
| 6 | 7 | http://www.mysql.com/ |<br />
| 6 | 7 | http://www.percona.com/ |<br />
| 7 | 8 | https://tools.percona.com/ |<br />
| 7 | 8 | https://mariadb.com/ |<br />
| 6 | 7 | http://planet.mysql.com/ |<br />
| 6 | 7 | http://dev.mysql.com/doc/ |<br />
+-------+--------+----------------------------+ <br />
</code></div>
<br />
<br />
These queries are just showing what the different aspects of the final query will be doing. So let us put it all together.<br />
<br />
<div class="codebox">
<code><br />
SELECT <br />
TRIM(TRAILING SUBSTRING(urldemo FROM LOCATE('.com', urldemo) + 4 )<br />
FROM SUBSTRING(urldemo FROM LOCATE('/', urldemo) + 2 ) ) AS parsed_domain ,<br />
urldemo as original_url<br />
FROM parse_example;<br />
+-------------------+----------------------------+<br />
| parsed_domain | original_url |<br />
+-------------------+----------------------------+<br />
| www.mysql.com | http://www.mysql.com/ |<br />
| www.percona.com | http://www.percona.com/ |<br />
| tools.percona.com | https://tools.percona.com/ |<br />
| mariadb.com | https://mariadb.com/ |<br />
| planet.mysql.com | http://planet.mysql.com/ |<br />
| dev.mysql.com | http://dev.mysql.com/doc/ |<br />
+-------------------+----------------------------+<br />
</code></div>
<br />
<br />
Now hopefully that helps you be able to parse out whatever you need. This example is limited to a url. But since some examples of functions already here is my example of a function that you can use to parse whatever you need.<br />
<br />
<br />
<div class="codebox">
<code><br />
CREATE FUNCTION PARSE_STRING(delimiterA VARCHAR(50), delimiterB VARCHAR(50), passed_string VARCHAR(255) )<br />
RETURNS VARCHAR(255) DETERMINISTIC<br />
RETURN <br />
TRIM(TRAILING SUBSTRING(passed_string FROM LOCATE(delimiterB, passed_string) ) <br />
FROM SUBSTRING(passed_string FROM LOCATE(delimiterA, passed_string) + CHAR_LENGTH(delimiterA) ) ) ;<br />
<br />
SELECT PARSE_STRING('//','.com', urldemo) FROM parse_example;<br />
+------------------------------------+<br />
| PARSE_STRING('//','.com', urldemo) |<br />
+------------------------------------+<br />
| www.mysql |<br />
| www.percona |<br />
| tools.percona |<br />
| mariadb |<br />
| planet.mysql |<br />
| dev.mysql |<br />
+------------------------------------+ <br />
</code></div>
<br />
<br />
Pull a last name from a full name field:<br />
<div class="codebox">
<code><br />
SELECT PARSE_STRING('John ','', 'John Smith') ; <br />
+----------------------------------------+<br />
| PARSE_STRING('John ','', 'John Smith') |<br />
+----------------------------------------+<br />
| Smith |<br />
+----------------------------------------+<br />
</code></div>
<br />
<br />
Pull the first name<br />
<div class="codebox">
<code><br />
SELECT PARSE_STRING('',' Smith', 'John Smith') ;<br />
+-----------------------------------------+<br />
| PARSE_STRING('',' Smith', 'John Smith') |<br />
+-----------------------------------------+<br />
| John |<br />
+-----------------------------------------+ <br />
</code></div>
<br />
<br />
Granted with the name examples you would need to know the delimiter values. But this is just an example you can build on. </div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com1tag:blogger.com,1999:blog-1806101152341830568.post-524680799352286372014-12-18T19:36:00.000-06:002014-12-18T19:36:51.460-06:00A MySQL PARTITION and SUBPARTITION Example<div dir="ltr" style="text-align: left;" trbidi="on">
So this is just a simple example of how to set up a PARTITION and a SUBPARTITION in MySQL. The concept here is that you have data in a table with numerous values in a datetime field. You might have data that is spread across numerous years (most likely you do). So one way to partition this data is to sort it by year but then also sort it by month within that yearly partition.<br />
<br />
Below is an example that you can use for consideration.<br />
<br />
Consider the test table. Your table with have many more fields of course. <br />
<div class="codebox">
<code> <br />
CREATE TABLE `t1` (<br />
`id` int(11) NOT NULL AUTO_INCREMENT,<br />
`date_time` datetime DEFAULT NOW(),<br />
PRIMARY KEY (`id`)<br />
) ENGINE=InnoDB;<br />
</code></div>
<br />
<br />
First I will populate the test table with random values for the date_time field.<br />
<div class="codebox">
<code> <br />
delimiter //<br />
CREATE PROCEDURE populate_t1( IN rowsofdata INT )<br />
BEGIN<br />
<br />
SET @A = 1;<br />
SET @B = 25 - @A;<br />
<br />
WHILE rowsofdata > 0 DO<br />
SELECT FLOOR( @A + (RAND() * @B )) INTO @randvalue; <br />
INSERT INTO t1<br />
SELECT NULL, NOW() - INTERVAL @randvalue MONTH;<br />
SET rowsofdata = rowsofdata - 1;<br />
END WHILE;<br />
END//<br />
delimiter ;<br />
call populate_t1(1000);<br />
</code></div>
<br />
<br />
Check to see what kind of values I ended up with:<br />
<div class="codebox">
<code> <br />
> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2012-01-01 00:00:00' AND '2013-01-01 00:00:00'\G<br />
*************************** 1. row ***************************<br />
COUNT(*): 43<br />
1 row in set (0.00 sec)<br />
<br />
> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2013-01-01 00:00:00' AND '2014-01-01 00:00:00'\G<br />
*************************** 1. row ***************************<br />
COUNT(*): 529<br />
1 row in set (0.00 sec)<br />
<br />
> SELECT COUNT(*) FROM t1 WHERE date_time BETWEEN '2014-01-01 00:00:00' AND NOW() \G<br />
*************************** 1. row ***************************<br />
COUNT(*): 428<br />
1 row in set (0.00 sec)<br />
</code></div>
<br />
<br />
Now I can alter the table so I can add my partitions and then test the values counts via the partition.<br />
<div class="codebox">
<code> <br />
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`date_time`), LOCK=SHARED;<br />
ALTER TABLE t1<br />
PARTITION BY RANGE( YEAR(date_time) )<br />
SUBPARTITION BY HASH(MONTH(date_time) ) (<br />
<br />
PARTITION p2012 VALUES LESS THAN (2013) (<br />
SUBPARTITION dec_2012,<br />
SUBPARTITION jan_2012,<br />
SUBPARTITION feb_2012,<br />
SUBPARTITION mar_2012,<br />
SUBPARTITION apr_2012,<br />
SUBPARTITION may_2012,<br />
SUBPARTITION jun_2012,<br />
SUBPARTITION jul_2012,<br />
SUBPARTITION aug_2012,<br />
SUBPARTITION sep_2012,<br />
SUBPARTITION oct_2012,<br />
SUBPARTITION nov_2012<br />
),<br />
<br />
PARTITION p2013 VALUES LESS THAN (2014) (<br />
SUBPARTITION dec_2013,<br />
SUBPARTITION jan_2013,<br />
SUBPARTITION feb_2013,<br />
SUBPARTITION mar_2013,<br />
SUBPARTITION apr_2013,<br />
SUBPARTITION may_2013,<br />
SUBPARTITION jun_2013,<br />
SUBPARTITION jul_2013,<br />
SUBPARTITION aug_2013,<br />
SUBPARTITION sep_2013,<br />
SUBPARTITION oct_2013,<br />
SUBPARTITION nov_2013<br />
<br />
),<br />
PARTITION p2014 VALUES LESS THAN (2015) (<br />
SUBPARTITION dec_2014,<br />
SUBPARTITION jan_2014,<br />
SUBPARTITION feb_2014,<br />
SUBPARTITION mar_2014,<br />
SUBPARTITION apr_2014,<br />
SUBPARTITION may_2014,<br />
SUBPARTITION jun_2014,<br />
SUBPARTITION jul_2014,<br />
SUBPARTITION aug_2014,<br />
SUBPARTITION sep_2014,<br />
SUBPARTITION oct_2014,<br />
SUBPARTITION nov_2014<br />
),<br />
<br />
PARTITION pmax VALUES LESS THAN MAXVALUE (<br />
SUBPARTITION dec_max,<br />
SUBPARTITION jan_max,<br />
SUBPARTITION feb_max,<br />
SUBPARTITION mar_max,<br />
SUBPARTITION apr_max,<br />
SUBPARTITION may_max,<br />
SUBPARTITION jun_max,<br />
SUBPARTITION jul_max,<br />
SUBPARTITION aug_max,<br />
SUBPARTITION sep_max,<br />
SUBPARTITION oct_max,<br />
SUBPARTITION nov_max<br />
)<br />
);<br />
</code></div>
<br />
<br />
My Show create table is very different now.<br />
<div class="codebox">
<code> <br />
> show create table t1;<br />
CREATE TABLE `t1` (<br />
`id` int(11) NOT NULL AUTO_INCREMENT,<br />
`date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,<br />
PRIMARY KEY (`id`,`date_time`)<br />
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1<br />
/*!50100 PARTITION BY RANGE ( YEAR(date_time))<br />
SUBPARTITION BY HASH (MONTH(date_time))<br />
(PARTITION p2012 VALUES LESS THAN (2013)<br />
(SUBPARTITION dec_2012 ENGINE = InnoDB,<br />
SUBPARTITION jan_2012 ENGINE = InnoDB,<br />
SUBPARTITION feb_2012 ENGINE = InnoDB,<br />
SUBPARTITION mar_2012 ENGINE = InnoDB,<br />
SUBPARTITION apr_2012 ENGINE = InnoDB,<br />
SUBPARTITION may_2012 ENGINE = InnoDB,<br />
SUBPARTITION jun_2012 ENGINE = InnoDB,<br />
SUBPARTITION jul_2012 ENGINE = InnoDB,<br />
SUBPARTITION aug_2012 ENGINE = InnoDB,<br />
SUBPARTITION sep_2012 ENGINE = InnoDB,<br />
SUBPARTITION oct_2012 ENGINE = InnoDB,<br />
SUBPARTITION nov_2012 ENGINE = InnoDB),<br />
PARTITION p2013 VALUES LESS THAN (2014)<br />
(SUBPARTITION dec_2013 ENGINE = InnoDB,<br />
SUBPARTITION jan_2013 ENGINE = InnoDB,<br />
SUBPARTITION feb_2013 ENGINE = InnoDB,<br />
SUBPARTITION mar_2013 ENGINE = InnoDB,<br />
SUBPARTITION apr_2013 ENGINE = InnoDB,<br />
SUBPARTITION may_2013 ENGINE = InnoDB,<br />
SUBPARTITION jun_2013 ENGINE = InnoDB,<br />
SUBPARTITION jul_2013 ENGINE = InnoDB,<br />
SUBPARTITION aug_2013 ENGINE = InnoDB,<br />
SUBPARTITION sep_2013 ENGINE = InnoDB,<br />
SUBPARTITION oct_2013 ENGINE = InnoDB,<br />
SUBPARTITION nov_2013 ENGINE = InnoDB),<br />
PARTITION p2014 VALUES LESS THAN (2015)<br />
(SUBPARTITION dec_2014 ENGINE = InnoDB,<br />
SUBPARTITION jan_2014 ENGINE = InnoDB,<br />
SUBPARTITION feb_2014 ENGINE = InnoDB,<br />
SUBPARTITION mar_2014 ENGINE = InnoDB,<br />
SUBPARTITION apr_2014 ENGINE = InnoDB,<br />
SUBPARTITION may_2014 ENGINE = InnoDB,<br />
SUBPARTITION jun_2014 ENGINE = InnoDB,<br />
SUBPARTITION jul_2014 ENGINE = InnoDB,<br />
SUBPARTITION aug_2014 ENGINE = InnoDB,<br />
SUBPARTITION sep_2014 ENGINE = InnoDB,<br />
SUBPARTITION oct_2014 ENGINE = InnoDB,<br />
SUBPARTITION nov_2014 ENGINE = InnoDB),<br />
PARTITION pmax VALUES LESS THAN MAXVALUE<br />
(SUBPARTITION dec_max ENGINE = InnoDB,<br />
SUBPARTITION jan_max ENGINE = InnoDB,<br />
SUBPARTITION feb_max ENGINE = InnoDB,<br />
SUBPARTITION mar_max ENGINE = InnoDB,<br />
SUBPARTITION apr_max ENGINE = InnoDB,<br />
SUBPARTITION may_max ENGINE = InnoDB,<br />
SUBPARTITION jun_max ENGINE = InnoDB,<br />
SUBPARTITION jul_max ENGINE = InnoDB,<br />
SUBPARTITION aug_max ENGINE = InnoDB,<br />
SUBPARTITION sep_max ENGINE = InnoDB,<br />
SUBPARTITION oct_max ENGINE = InnoDB,<br />
SUBPARTITION nov_max ENGINE = InnoDB)) <br />
</code></div>
<br />
<br />
So can we still count our value as expected?<br />
<div class="codebox">
<code> <br />
> SELECT count(*) FROM t1 PARTITION (p2012) \G<br />
*************************** 1. row ***************************<br />
count(*): 43<br />
> SELECT count(*) FROM t1 PARTITION (p2013) \G<br />
*************************** 1. row ***************************<br />
count(*): 529<br />
> SELECT count(*) FROM t1 PARTITION (p2014) \G<br />
*************************** 1. row ***************************<br />
count(*): 428<br />
</code></div>
<br />
<br />
So far so good, all the values matched up to the count we had before. So we can also count or select per the subpartition.<br />
<br />
<div class="codebox">
<code> <br />
> SELECT * FROM t1 PARTITION (dec_2012) limit 5;<br />
+-----+---------------------+<br />
| id | date_time |<br />
+-----+---------------------+<br />
| 59 | 2012-12-19 00:59:57 |<br />
| 68 | 2012-12-19 00:59:58 |<br />
| 93 | 2012-12-19 00:59:59 |<br />
| 105 | 2012-12-19 00:59:59 |<br />
| 111 | 2012-12-19 00:59:59 |<br />
+-----+---------------------+<br />
<br />
> SELECT * FROM t1 PARTITION (jan_2013) limit 5;<br />
+-----+---------------------+<br />
| id | date_time |<br />
+-----+---------------------+<br />
| 6 | 2013-01-19 00:59:55 |<br />
| 29 | 2013-01-19 00:59:56 |<br />
| 55 | 2013-01-19 00:59:57 |<br />
| 79 | 2013-01-19 00:59:58 |<br />
| 100 | 2013-01-19 00:59:59 |<br />
+-----+---------------------+<br />
<br />
> SELECT * FROM t1 PARTITION (jan_2014) limit 5;<br />
+-----+---------------------+<br />
| id | date_time |<br />
+-----+---------------------+<br />
| 16 | 2014-01-19 00:59:55 |<br />
| 190 | 2014-01-19 01:00:04 |<br />
| 191 | 2014-01-19 01:00:04 |<br />
| 229 | 2014-01-19 01:00:05 |<br />
| 234 | 2014-01-19 01:00:06 |<br />
+-----+---------------------+<br />
<br />
> SELECT * FROM t1 PARTITION (jun_2014) limit 5;<br />
+-----+---------------------+<br />
| id | date_time |<br />
+-----+---------------------+<br />
| 13 | 2014-06-19 00:59:55 |<br />
| 189 | 2014-06-19 01:00:04 |<br />
| 221 | 2014-06-19 01:00:05 |<br />
| 222 | 2014-06-19 01:00:05 |<br />
| 238 | 2014-06-19 01:00:06 |<br />
+-----+---------------------+<br />
<br />
> SELECT * FROM t1 PARTITION (dec_2013) limit 5;<br />
+-----+---------------------+<br />
| id | date_time |<br />
+-----+---------------------+<br />
| 50 | 2013-12-19 00:59:57 |<br />
| 74 | 2013-12-19 00:59:58 |<br />
| 98 | 2013-12-19 00:59:59 |<br />
| 107 | 2013-12-19 00:59:59 |<br />
| 167 | 2013-12-19 01:00:02 |<br />
+-----+---------------------+<br />
</code></div>
<br />
<br />
This is great and handy but what happens when the year 2015 or 2016 comes around? All of that data would be in the pmax partition. So how do we add a new partition in between p2014 and pmax?<br />
<br />
If you had no data in pmax you could drop it and add a new partition onto the end. But it is just as easy to reorganize the partition. This will take the pmax partition and alter it into our new partitions. <br />
<br />
<div class="codebox">
<code> <br />
ALTER TABLE t1 REORGANIZE PARTITION pmax INTO (<br />
PARTITION p2015 VALUES LESS THAN (2016) (<br />
SUBPARTITION dec_2015,<br />
SUBPARTITION jan_2015,<br />
SUBPARTITION feb_2015,<br />
SUBPARTITION mar_2015,<br />
SUBPARTITION apr_2015,<br />
SUBPARTITION may_2015,<br />
SUBPARTITION jun_2015,<br />
SUBPARTITION jul_2015,<br />
SUBPARTITION aug_2015,<br />
SUBPARTITION sep_2015,<br />
SUBPARTITION oct_2015,<br />
SUBPARTITION nov_2015<br />
),<br />
PARTITION pmax VALUES LESS THAN MAXVALUE (<br />
SUBPARTITION dec_max,<br />
SUBPARTITION jan_max,<br />
SUBPARTITION feb_max,<br />
SUBPARTITION mar_max,<br />
SUBPARTITION apr_max,<br />
SUBPARTITION may_max,<br />
SUBPARTITION jun_max,<br />
SUBPARTITION jul_max,<br />
SUBPARTITION aug_max,<br />
SUBPARTITION sep_max,<br />
SUBPARTITION oct_max,<br />
SUBPARTITION nov_max<br />
)<br />
); <br />
</code></div>
<br />
<br />
Hope this helps, best of luck. </div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-66171437443322738952014-11-27T14:14:00.000-06:002014-11-27T14:14:56.109-06:00Recover Lost MySQL data with mysqlbinlog point-in-time-recovery example<div dir="ltr" style="text-align: left;" trbidi="on">
Backup ... backup... Backup... but of course.. you also need to monitor and test those backups often otherwise they could be worthless. Having your MySQL <a href="http://dev.mysql.com/doc/refman/5.6/en/binary-log.html">binlogs</a> enabled can certainly help you in times of an emergency as well. The MySQL binlogs are often referenced in regards to MySQL replication, for a good reason, they store all of the queries or events that alter data (<a href="http://dev.mysql.com/doc/refman/5.6/en/binary-log-formats.html">row-based</a> is a little different but this an example). The binlogs have a minimal impact on server performance when considering the recovery options they provide. <br />
<br />
<div class="codebox">
<code><br />
[anothermysqldba]> show variables like 'log_bin%';<br />
+---------------------------------+--------------------------------------------+<br />
| Variable_name | Value |<br />
+---------------------------------+--------------------------------------------+<br />
| log_bin | ON |<br />
| log_bin_basename | /var/lib/mysql/binlogs/mysql-binlogs |<br />
| log_bin_index | /var/lib/mysql/binlogs/mysql-binlogs.index |<br />
<br />
show variables like 'binlog_format%';<br />
+---------------+-------+<br />
| Variable_name | Value |<br />
+---------------+-------+<br />
| binlog_format | MIXED |<br />
+---------------+-------+<br />
</code></div>
<br />
<br />
So this is just a simple example using mysqlbinlog to recover data from a binlog and apply it back to the database. <br />
<br />
First we need something to loose. If something was to happen to our database we need to be able to recover the data or maybe it is just a way to recover from someones mistake. <br />
<br />
<div class="codebox">
<code><br />
CREATE TABLE `table_w_rdata` (<br />
`id` int(11) NOT NULL AUTO_INCREMENT,<br />
`somedata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, <br />
`moredata` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,<br />
PRIMARY KEY (`id`)<br />
) ENGINE=InnoDB;<br />
</code></div>
<br />
We can pretend here and assume that we have developers/DBAs that are
not communicating very well and/or saving copies of their code.<br />
<br />
<div class="codebox">
<code><br />
delimiter //<br />
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )<br />
BEGIN<br />
<br />
SET @A = 3; <br />
SET @B = 15 - @A;<br />
SET @C = 16; <br />
SET @D = 25 - @C; <br />
<br />
WHILE rowsofdata > 0 DO<br />
INSERT INTO table_w_rdata <br />
SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D ))) AS moredata ;<br />
SET rowsofdata = rowsofdata - 1;<br />
END WHILE;<br />
END//<br />
delimiter ;<br />
call populate_dummydata(50);<br />
<br />
> SELECT NOW() \G<br />
*************************** 1. row ***************************<br />
NOW(): 2014-11-27 17:32:25<br />
1 row in set (0.00 sec)<br />
<br />
> SELECT * from table_w_rdata WHERE id > 45;<br />
+----+----------------------------+------------------+<br />
| id | somedata | moredata |<br />
+----+----------------------------+------------------+<br />
| 46 | b204e9800998ecf8427e | 0998ecf8427e |<br />
| 47 | d98f00b204e9800998ecf8427e | 8ecf8427e |<br />
| 48 | b204e9800998ecf8427e | 800998ecf8427e |<br />
| 49 | 98f00b204e9800998ecf8427e | e9800998ecf8427e |<br />
| 50 | 98f00b204e9800998ecf8427e | 998ecf8427e |<br />
+----+----------------------------+------------------+<br />
</code></div>
<br />
While one
procedure is created it is later written over by someone else incorrectly. <br />
<div class="codebox">
<code><br />
DROP PROCEDURE IF EXISTS populate_dummydata ;<br />
delimiter //<br />
CREATE PROCEDURE populate_dummydata( IN rowsofdata INT )<br />
BEGIN<br />
<br />
SET @A = 3;<br />
SET @B = 15 - @A;<br />
SET @C = 16;<br />
SET @D = 25 - @C;<br />
<br />
WHILE rowsofdata > 0 DO<br />
INSERT INTO table_w_rdata<br />
SELECT NULL, SUBSTR(md5(''),FLOOR( @C + (RAND() * @A ))) as somedata, SUBSTR(md5(''),FLOOR( @B + (RAND() * @D ))) AS moredata ;<br />
SET rowsofdata = rowsofdata - 1;<br />
END WHILE;<br />
END//<br />
delimiter ;<br />
<br />
call populate_dummydata(50);<br />
> SELECT NOW(); SELECT * from table_w_rdata WHERE id > 95;<br />
+---------------------+<br />
| NOW() |<br />
+---------------------+<br />
| 2014-11-27 17:36:28 |<br />
+---------------------+<br />
1 row in set (0.00 sec)<br />
<br />
+-----+-------------------+---------------------+<br />
| id | somedata | moredata |<br />
+-----+-------------------+---------------------+<br />
| 96 | 4e9800998ecf8427e | 00998ecf8427e |<br />
| 97 | 9800998ecf8427e | 800998ecf8427e |<br />
| 98 | e9800998ecf8427e | 204e9800998ecf8427e |<br />
| 99 | e9800998ecf8427e | 4e9800998ecf8427e |<br />
| 100 | 9800998ecf8427e | 04e9800998ecf8427e |<br />
+-----+-------------------+---------------------+<br />
</code></div>
<br />
<br />
The replaced version of the procedure is not generating random values like the team wanted. The original creator of the procedure just quit from frustration. So what to do? A little time has past since it was created as well. We do know the database name, routine name and the general time frame when the incorrect procedure was created and lucky for us the bin logs are still around, so we can go get it.<br />
<br />
We have to take a general look around since we just want a <a href="http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html">point-in-time-recovery</a> of this procedure.We happen to find the procedure and the position in the binlog before and after it. <br />
<br />
<div class="codebox">
<code><br />
NOW(): 2014-11-27 19:46:17<br />
# mysqlbinlog --start-datetime=20141127173200 --stop-datetime=20141127173628 --database=anothermysqldba mysql-binlogs.000001 | more<br />
<br />
at 253053<br />
at 253564<br />
<br />
# mysql anothermysqldba <a href="http://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html">--login-path</a>=local -e "DROP PROCEDURE populate_dummydata";<br />
# mysqlbinlog --start-position=253053 --stop-position=253564 --database=anothermysqldba mysql-binlogs.000001 | mysql <a href="http://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html">--login-path</a>=local anothermysqldba<br />
<br />
<br />
> SHOW CREATE PROCEDURE populate_dummydata\G<br />
*************************** 1. row ***************************<br />
Procedure: populate_dummydata<br />
sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION<br />
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `populate_dummydata`( IN rowsofdata INT )<br />
BEGIN<br />
<br />
SET @A = 3;<br />
SET @B = 15 - @A;<br />
SET @C = 16;<br />
SET @D = 25 - @C;<br />
<br />
WHILE rowsofdata > 0 DO<br />
INSERT INTO table_w_rdata<br />
SELECT NULL, SUBSTR(md5(''),FLOOR( @A + (RAND() * @B ))) as somedata, SUBSTR(md5(''),FLOOR( @C + (RAND() * @D ))) AS moredata ;<br />
SET rowsofdata = rowsofdata - 1;<br />
END WHILE;<br />
END<br />
character_set_client: utf8<br />
collation_connection: utf8_general_ci<br />
Database Collation: latin1_swedish_ci<br />
1 row in set (0.00 sec)<br />
<br />
NOW(): 2014-11-27 19:51:03<br />
> call populate_dummydata(50); <br />
> SELECT * from table_w_rdata WHERE id > 145;<br />
+-----+-----------------------------+------------------+<br />
| id | somedata | moredata |<br />
+-----+-----------------------------+------------------+<br />
| 146 | 98f00b204e9800998ecf8427e | 800998ecf8427e |<br />
| 147 | cd98f00b204e9800998ecf8427e | 800998ecf8427e |<br />
| 148 | 204e9800998ecf8427e | 98ecf8427e |<br />
| 149 | d98f00b204e9800998ecf8427e | e9800998ecf8427e |<br />
| 150 | 204e9800998ecf8427e | 9800998ecf8427e |<br />
+-----+-----------------------------+------------------+<br />
</code></div>
<br />
<br />
We recovered our procedure from the binary log via <a href="http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html">point-in-time-recovery</a>.<br />
This is a simple example but it is an example of the tools you can use moving forward.<br />
<br />
This is why the binlogs are so valuable. <br />
<br />
Helpful URL:<br />
<ul style="text-align: left;">
<li><a href="http://dev.mysql.com/doc/refman/5.6/en/binary-log.html">http://dev.mysql.com/doc/refman/5.6/en/binary-log.html</a></li>
<li><a href="http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html">http://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html</a></li>
</ul>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-4808958368995636442014-11-11T16:23:00.001-06:002014-11-11T16:23:23.394-06:00systemctl and MySQL <div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
So some users complete a yum install of MySQL and expect to be able to use the following command to start the MySQL server:: /etc/init.d/mysql start only to get "No such file or directory"</div>
<br />
<div style="text-align: left;">
So this is a quick post to help use the systemctl command. </div>
<div style="text-align: left;">
You are likely to see this:</div>
<div style="text-align: left;">
<div class="codebox">
<code>
# systemctl list-unit-files | grep mysql<br />
mysqld.service disabled</code></div>
</div>
<br />
<div style="text-align: left;">
First I would recommend go to <a href="http://tools.percona.com/">tools.percona.com</a> and create a valid my.cnf file. </div>
<br />
<div style="text-align: left;">
So the solution is easy, we just need to enable this so the database can start on server start up.</div>
<div style="text-align: left;">
<br />
<div class="codebox">
<code>
#systemctl enable mysqld.service<br />
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/mysql.service'<br />
ln -s '/usr/lib/systemd/system/mysqld.service' '/etc/systemd/system/multi-user.target.wants/mysqld.service'<br />
#systemctl start mysqld<br />
#systemctl list-unit-files | grep mysql<br />
mysql.service enabled <br />
mysqld.service enabled
</code></div>
</div>
<br />
<div style="text-align: left;">
Now you should be good to go...</div>
</div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0tag:blogger.com,1999:blog-1806101152341830568.post-25244741356675520782014-10-11T12:27:00.000-05:002014-10-11T12:27:14.980-05:00Rotating MySQL Slow Logs <div dir="ltr" style="text-align: left;" trbidi="on">
While working with different clients I happen to run across very large slow log files from time to time. While several opinions on how they should be rotated exist. Many of these opinions use log rotate and the flush logs command, I prefer not to flush my binary logs though. This is why I agree with Ronald Bradford's <a href="http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/">blog post</a> from years ago on how to do this. <br />
<ul style="text-align: left;">
<li><a href="http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/">http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/</a></li>
</ul>
I have taken it a little further and scripted the steps. The bash script is built with MySQL 5.6 and the mysql_config_editor in mind it can be used on older versions of MySQL as well.<br />
<br />
The script will do the following:<br />
<ul style="text-align: left;">
<li>Gather current log file name</li>
<li>Gather current long query time value</li>
<li>Resets the long query time to a higher value</li>
<li>Copies the log while truncating it as well (See Ronald's Post)</li>
<li>Resets the long query time back to the original time</li>
<li>Executes a simple slow query so you can check the new slow log if you wish</li>
<li>Removes the older slow log so you can gain space back. </li>
<ul>
<li>You can comment this command out if you wish to review the log instead.</li>
</ul>
</ul>
So does it all work ?<br />
Well let us use this example.<br />
<br />
I am currently using the mysql_config_editor over a .my.cnf file so I updated the script accordingly. <br />
<div class="codebox">
<code>
# mysql_config_editor print --all<br />
[local]<br />
user = root<br />
password = *****<br />
host = localhost <br />
</code></div>
<br />
I can see that this slow query log is now 1G.<br />
<div class="codebox">
<code>
# ls -alh mysql-slow.log<br />
-rw-rw---- 1 mysql mysql 1.1G Oct 11 16:08 mysql-slow.log <br />
</code></div>
<br />
So I execute the script<br />
<div class="codebox">
<code>
# /root/rotate_slow_logs.sh <br />
# ls -alh mysql-slow.log<br />
-rw-rw---- 1 mysql mysql 5.8K Oct 11 16:11 mysql-slow.log<br />
</code></div>
<br />
Ok good, it worked and I have a smaller log file now without flushing my bin logs or restarting MySQL.<br />
<br />
This script can be added to a crontab so you can rotate as often as you would like.<br />
<br />
Here is the script.<br />
<div class="codebox">
<code>
#!/bin/bash<br />
<br />
# THIS IS BUILT WITH MYSQL 5.6 SECURITY IN MIND.<br />
# SET THE LOGINPATHVALUE if you are using the mysql_config_editor<br />
# IF YOU ARE NOT USING THE mysql_config_editor THEN IT IS ASSUMED YOU HAVE<br />
# SET A .my.cnf FILE IN THE USER HOME DIR OR THIS USER HAS NO PASSWORD SET<br />
<br />
<br />
# PLEASE SET THIS ACCORDINGLY TO YOUR SYSTEM.<br />
LOGINPATHVALUE="local";<br />
<br />
if [ -z "${LOGINPATHVALUE}" ]; then<br />
LOGINPATH="";<br />
fi<br />
<br />
if [ -n "${LOGINPATHVALUE-unset}" ]; then<br />
LOGINPATH="--login-path=$LOGINPATHVALUE "<br />
<br />
fi<br />
<br />
# GATHERS THE LOG FILE NAME<br />
SLOWLOG=$(mysqladmin $LOGINPATH variables | grep slow | grep file | awk '/[a-zA-Z]/ {print $4}' )<br />
<br />
# GATHER CURRENT VALUE<br />
LQT=$( mysqladmin $LOGINPATH variables | grep long_query_time | awk '/[0-9]./ {print $4}' )<br />
LQTB=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *200 AS LQTB;" | awk '/[0-9]./ {print $1}' )<br />
LQTC=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *2 AS LQTC;" | awk '/[0-9]./ {print $1}' )<br />
<br />
# GATHER MARKER<br />
DATE=`date +"%m%d%Y"`<br />
<br />
# RESET SLOW QUERY TIME<br />
# SET GLOBAL long_query_time=10;<br />
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQTB" <br />
<br />
LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time | awk '/[0-9]./ {print $4}' )<br />
<br />
#MOVE THE LOG OUT<br />
cp $SLOWLOG $SLOWLOG.$DATE; > $SLOWLOG<br />
<br />
#SET THE TIMEBACK<br />
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQT"<br />
<br />
LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time | awk '/[0-9]./ {print $4}' )<br />
<br />
#PLACE A Slow query for log<br />
SLOWQUERY=$(mysql $LOGINPATH -e "SELECT sleep($LQTC) " )<br />
<br />
# REMOVE OLD LOG<br />
/bin/rm -f $SLOWLOG.$DATE;</code></div>
<br />
<br />
<br />
<br /></div>
Keith Larson ( anothermysqldba )http://www.blogger.com/profile/10894464761448543094noreply@blogger.com0