MySQL Group Replication

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.

• https://dev.mysql.com/doc/refman/8.0/en/group-replication.html
• https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html

Below is an example of how to set this up and a few pain point examples as I poked around with it.
I am using three different servers,

 Server CENTOSA


mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.17:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER repl@'%' IDENTIFIED BY 'replpassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;


CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='replpassword'
FOR CHANNEL 'group_replication_recovery';


mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)


mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.11 sec)


mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT * FROM performance_schema.replication_group_members \G

*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1
MEMBER_HOST: centosa
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.15



So now we can add more servers.
Server CENTOSB

• https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE


transaction_write_set_extraction=XXHASH64
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.89:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> CHANGE MASTER TO
MASTER_USER='repl',
MASTER_PASSWORD='replpassword'
FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected (0.02 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (4.03 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | RECOVERING | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)


Server CENTOSC

•  https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html

vi my.cnf
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

transaction_write_set_extraction=XXHASH64
group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8"
group_replication_start_on_boot=off
group_replication_local_address= "192.168.111.124:33061"
group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061"
group_replication_bootstrap_group=off

mysql> CHANGE MASTER TO
-> MASTER_USER='repl',
-> MASTER_PASSWORD='replpassword'
-> FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;
Query OK, 0 rows affected (0.02 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.58 sec)
mysql> SELECT * FROM performance_schema.replication_group_members \G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1
MEMBER_HOST: centosa
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.15

*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: 572ca2fa-5eff-11e9-8df9-08002712f4b1
MEMBER_HOST: centosb
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.15

*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
MEMBER_ID: c5f3d1d2-8dd8-11e9-858d-08002773d1b6
MEMBER_HOST: centosc
MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.15
3 rows in set (0.00 sec)


So this is all great but it doesn't always mean they go online, they can often sit in recovery mode.
I have seen this fail with MySQL crashes so far so need to ensure it stable.

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>

Side Note to address some of those factors --

mysql> START GROUP_REPLICATION;
ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start.

mysql> reset slave all;
Query OK, 0 rows affected (0.03 sec)



-- Then start over from Change master command

mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

[ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Error on opening a connection to 192.168.111.17:33061 on local port: 33061.'
[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 >
[ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'


https://ronniethedba.wordpress.com/2017/04/22/this-member-has-more-executed-transactions-than-those-present-in-the-group/ 

 [ERROR] [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error during the recovery process of Group Replication. The server will leave the group.'
[ERROR] [MY-013173] [Repl] Plugin group_replication reported: 'The plugin encountered a critical error and will abort: Fatal error during execution of Group Replication'

SELECT * FROM performance_schema.replication_connection_status\G

My thoughts...
Keep in mind that group replication can be set up in single primary mode or multi-node

mysql> select @@group_replication_single_primary_mode\G
*************************** 1. row ***************************
@@group_replication_single_primary_mode: 1

mysql> create database testcentosb;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement


you will of course get an error if you write to none primary node.

group-replication-single-primary-mode=off  <-- added to the cnf files. 

mysql> SELECT * FROM performance_schema.replication_group_members;

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa     |        3306 | RECOVERING   | PRIMARY     | 8.0.15         |

| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb     |        3306 | ONLINE       | PRIMARY     | 8.0.15         |

| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc     |        3306 | RECOVERING   | PRIMARY     | 8.0.15         |

+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

3 rows in set (0.00 sec)

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.

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | SECONDARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

[root@centosa]# systemctl stop mysqld

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

[root@centosa]# systemctl start mysqld
[root@centosa]# mysql
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.34 sec)

mysql> SELECT * FROM performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | SECONDARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)


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.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | SECONDARY | 8.0.15 |
| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 |
| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)


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.

URLS of Interest

https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

https://dev.mysql.com/doc/refman/8.0/en/group-replication-deploying-in-single-primary-mode.html

http://datacharmer.blogspot.com/2017/01/mysql-group-replication-vs-multi-source.html 

https://dev.mysql.com/doc/refman/8.0/en/group-replication-launching.html

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-adding-instances.html

https://ronniethedba.wordpress.com/2017/04/22/how-to-setup-mysql-group-replication/

https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-group-replication-on-ubuntu-16-04 

https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replication_group_seeds 

https://bugs.mysql.com/bug.php?id=90534

https://www.percona.com/blog/2017/02/24/battle-for-synchronous-replication-in-mysql-galera-vs-group-replication/

https://lefred.be/content/mysql-group-replication-is-sweet-but-can-be-sour-if-you-misunderstand-it/

https://www.youtube.com/watch?v=IfZK-Up03Mw

https://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/

Every MySQL should have these variables set ...

So over the years, we all learn more and more about what we like and use often in MySQL. 

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. 

At a high level:

• >Move the Slow log to a table 
• Set report_host_name 
• Set master & slaves to use tables
• Turn off log_queries_not_using_indexes until needed 
• Side note -- USE  ALGORITHM=INPLACE
• Side note -- USE mysql_config_editor
• Side note -- USE  mysql_upgrade  --upgrade-system-tables

Move the Slow log to a table 

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. 


mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|       0 |
+----------+
1 row in set (0.00 sec)

mysql> select @@slow_query_log,@@sql_log_off;
+------------------+---------------+
| @@slow_query_log | @@sql_log_off |
+------------------+---------------+
|                1 |            0 |
+------------------+---------------+

mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)

mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)

mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)

mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)

mysql> set GLOBAL slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set GLOBAL sql_log_off=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|       0 |
+----------+
1 row in set (0.00 sec)
mysql> select @@slow_launch_time;
+--------------------+
| @@slow_launch_time |
+--------------------+
|                   2 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT SLEEP(10);
+-----------+
| SLEEP(10) |
+-----------+
|         0 |
+-----------+
1 row in set (9.97 sec)

mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|         1 |
+----------+
1 row in set (0.00 sec)

mysql> select * from   mysql.slow_log\G
*************************** 1. row ***************************
    start_time: 2019-03-27 18:02:32
     user_host: klarson[klarson] @ localhost []
    query_time: 00:00:10
     lock_time: 00:00:00
     rows_sent: 1
rows_examined: 0
            db:
last_insert_id: 0
     insert_id: 0
     server_id: 502
      sql_text: SELECT SLEEP(10)
     thread_id: 16586457


Now you can truncate it or dump it or whatever you like to do with this data easily also.
Note variable values into your my.cnf file to enable upon restart.


Set report_host_name 

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.


report_host                     = <hostname>  <or whatever you want to call it>


This allows you from the master to do


mysql> show slave hosts;
+-----------+-------------+------+-----------+--------------------------------------+
| Server_id | Host         | Port | Master_id | Slave_UUID                           |
+-----------+-------------+------+-----------+--------------------------------------+
|   21235302 | <hostname>  | 3306 |   

21235301| a55faa32-c832-22e8-b6fb-e51f15b76554 |
+-----------+-------------+------+-----------+--------------------------------------+

Set master & slaves to use tables


mysql> show variables like '%repository';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository     | FILE   |
| relay_log_info_repository | FILE   |
+---------------------------+-------+

mysql_slave> stop slave;
mysql_slave> SET GLOBAL master_info_repository = 'TABLE'; 
mysql_slave> SET GLOBAL relay_log_info_repository = 'TABLE'; 
mysql_slave> start slave;



Make sure you add to my.cnf to you do not lose binlog and position at a restart. It will default to FILE otherwise.

• master-info-repository =TABLE 
• relay-log-info-repository =TABLE


mysql> show variables like '%repository';
---------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| master_info_repository     | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+



All data is available in tables now and easily stored with backups


mysql> desc mysql.slave_master_info;
+------------------------+---------------------+------+-----+---------+-------+
| Field                   | Type                 | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| Number_of_lines         | int(10) unsigned     | NO   |     | NULL     |       |
| Master_log_name         | text                 | NO   |     | NULL     |       |
| Master_log_pos         | bigint(20) unsigned | NO   |     | NULL     |       |
| Host                   | char(64)             | YES   |     | NULL     |       |
| User_name               | text                 | YES   |     | NULL     |       |
| User_password           | text                 | YES   |     | NULL     |       |
| Port                   | int(10) unsigned     | NO   |     | NULL     |       |
| Connect_retry           | int(10) unsigned     | NO   |     | NULL     |       |
| Enabled_ssl             | tinyint(1)           | NO   |     | NULL     |       |
| Ssl_ca                 | text                 | YES   |     | NULL     |       |
| Ssl_capath             | text                 | YES   |     | NULL     |       |
| Ssl_cert               | text                 | YES   |     | NULL     |       |
| Ssl_cipher             | text                 | YES   |     | NULL     |       |
| Ssl_key                 | text                 | YES   |     | NULL     |       |
| Ssl_verify_server_cert | tinyint(1)           | NO   |     | NULL     |       |
| Heartbeat               | float               | NO   |     | NULL     |       |
| Bind                   | text                 | YES   |     | NULL     |       |
| Ignored_server_ids     | text                 | YES   |     | NULL     |       |
| Uuid                   | text                 | YES   |     | NULL     |       |
| Retry_count             | bigint(20) unsigned | NO   |     | NULL     |       |
| Ssl_crl                 | text                 | YES   |     | NULL     |       |
| Ssl_crlpath             | text                 | YES   |     | NULL     |       |
| Enabled_auto_position   | tinyint(1)           | NO   |     | NULL     |       |
| Channel_name           | char(64)             | NO   | PRI | NULL     |       |
| Tls_version             | text                 | YES   |     | NULL     |       |
| Public_key_path         | text                 | YES   |     | NULL     |       |
| Get_public_key         | tinyint(1)           | NO   |     | NULL     |       |
+------------------------+---------------------+------+-----+---------+-------+
27 rows in set (0.05 sec)

mysql> desc mysql.slave_relay_log_info;
+-------------------+---------------------+------+-----+---------+-------+
| Field             | Type                 | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| Number_of_lines   | int(10) unsigned     | NO   |     | NULL     |       |
| Relay_log_name     | text                 | NO   |     | NULL     |       |
| Relay_log_pos     | bigint(20) unsigned | NO   |     | NULL     |       |
| Master_log_name   | text                 | NO   |     | NULL     |       |
| Master_log_pos     | bigint(20) unsigned | NO   |     | NULL     |       |
| Sql_delay         | int(11)             | NO   |     | NULL     |       |
| Number_of_workers | int(10) unsigned     | NO   |     | NULL     |       |
| Id                 | int(10) unsigned     | NO   |     | NULL     |       |
| Channel_name       | char(64)             | NO   | PRI | NULL     |       |

+-------------------+---------------------+------+-----+---------+-------+




Turn off log_queries_not_using_indexes until needed 

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.


mysql> SET GLOBAL log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)

To turn on 


mysql> SET GLOBAL log_queries_not_using_indexes=1;

Query OK, 0 rows affected (0.00 sec)


Note variable values into your my.cnf file to enable upon restart. 


Side note -- USE  ALGORITHM=INPLACE 

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 ALGORITHM=INPLACE or  ALGORITHM=COPY when altering tables. 


mysql> ALTER TABLE TABLE_DEMO   ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0   Duplicates: 0   Warnings: 0



mysql> ALTER TABLE TABLE_DEMO   ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0   Duplicates: 0   Warnings: 0


A list of online DLL operations is here

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html 

Side note -- USE mysql_config_editor

Previous blog post about this is here 

https://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html  

The simple example


mysql_config_editor set  --login-path=local --host=localhost --user=root --password
Enter password:
# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost

# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


# mysql  --login-path=local
Welcome to the MySQL monitor.

# mysql  --login-path=local -e 'SELECT NOW()';


Side note -- USE  mysql_upgrade  --upgrade-system-tables

Don't forget to use mysql_upgrade after you actually upgrade. 

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. 

mysql_upgrade --login-path=local  --upgrade-system-tables

A look at MySQL 5.7 DMR

So I figured it was about time I looked at MySQL 5.7. This is a high level overview, but I was looking over the MySQL 5.7 in a nutshell document:

• http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

So I am starting with a fresh Fedora 20 (Xfce) install.
Overall, I will review a few items that I found curious and interesting with MySQL 5.7. The nutshell has a lot of information so well worth a review.

I downloaded the MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar

The install was planned on doing the following

# tar -vxf MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar
# rm -f mysql-community-embedded*
]# ls -a MySQL-*.rpm
MySQL-client-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-embedded-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-shared-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-devel-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-server-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
MySQL-test-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm
# yum -y install MySQL-*.rpm
Complete!


While it said Complete I also noticed an error. It should have not finished the install if it found an error but ok....

FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db:
Data::Dumper


This error was confirmed .. 

# /etc/init.d/mysql start
Starting MySQL............ ERROR! The server quit without updating PID file
# tail /var/lib/mysql/fedora20mysql57.localdomain.err
ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
# /usr/bin/mysql_install_db
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db:
Data::Dumper
# yum -y install perl-Data-Dumper
# /usr/bin/mysql_install_db
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
# chown -R mysql:mysql /var/lib/mysql/mysql/
# cat /root/.mysql_secret
# mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepassword');
Query OK, 0 rows affected (0.01 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.4-m14 |
+-----------+


A more robust process for upgrades and etc is documented here:
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html
Check to ensure you have GLIBC_2.15 if you plan to install this on your OS.

OK so now that it is installed, what do we have.

• Security improvements
• Installation creates no anonymous-user accounts

mysql> select User , Host,plugin from mysql.user \G
*************************** 1. row ***************************
  User: root
  Host: localhost
plugin: mysql_native_password


• Installation creates no test database.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+


• Password Expiration Policy  

mysql> SELECT @@default_password_lifetime \G
*************************** 1. row ***************************
@@default_password_lifetime: 360


These are all long overdue improvements, and thank you all for the improvements.
So now to look over the rest, we at least want some kind of data and schema. So I will install the world database for the tests. 

# wget http://downloads.mysql.com/docs/world_innodb.sql.gz
# gzip -d world_innodb.sql.gz
# mysql -u root -p -e "create database world";
# mysql -u root -p world < world_innodb.sql


• Online
• ALTER TABLE.  ALTER TABLE now supports a RENAME INDEX clause that renames an index

# mysql -u root -p world
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
mysql> ALTER TABLE City ALGORITHM=INPLACE, RENAME KEY CountryCode TO THECountryCode;
Query OK
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB



• InnoDB enhancements 
  • VARCHAR size may be increased using an in-place ALTER TABLE
  
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
mysql> ALTER TABLE City ALGORITHM=COPY, CHANGE COLUMN Name Name VARCHAR(35);
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(35) DEFAULT NULL,
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB 
mysql> ALTER TABLE City ALGORITHM=INPLACE, CHANGE COLUMN Name Name VARCHAR(255);
mysql> show create table City;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `THECountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB
  • InnoDB temporary table metadata is no longer stored to InnoDB system tables. The table is created when the first SELECT statement is run against it.
  
mysql> select * from INNODB_TEMP_TABLE_INFO;
mysql> show create table INNODB_TEMP_TABLE_INFO;
CREATE TEMPORARY TABLE `INNODB_TEMP_TABLE_INFO` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(202) DEFAULT NULL,
  `N_COLS` int(11) unsigned NOT NULL DEFAULT '0',
  `SPACE` int(11) unsigned NOT NULL DEFAULT '0',
  `PER_TABLE_TABLESPACE` varchar(64) DEFAULT NULL,
  `IS_COMPRESSED` varchar(64) DEFAULT NULL
) ENGINE=MEMORY
CODE HERE

  • InnoDB now supports MySQL-supported spatial data types
  • This is HUGE. Because it has been one of the reasons people picked PostgreSQL over MySQL.
  • http://www.oracle.com/us/corporate/press/2180737
  • http://dev.mysql.com/doc/refman/5.7/en/mysql-spatial-datatypes.html
  • http://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html
  • http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/
  • There is now a separate tablespace for all non-compressed InnoDB temporary tables
  mysql> select @@innodb_temp_data_file_path \G
*************************** 1. row ***************************
@@innodb_temp_data_file_path: ibtmp1:12M:autoextend

• Condition handling ::
  GET STACKED DIAGNOSTICS

mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'
mysql> GET DIAGNOSTICS CONDITION 1  @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.45 sec)

mysql> SELECT @p1, @p2 \G
*************************** 1. row ***************************
@p1: 42S02
@p2: Unknown table 'test.no_such_table'
1 row in set (0.01 sec)

• Triggers
  The trigger limitation has been lifted and multiple triggers are permitted. Please see the documentation as they give a good example. I will demo it some here just to show that multiple triggers on a single table are possible.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account
    ->    FOR EACH ROW PRECEDES ins_sum
    ->    SET
    ->    @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
    ->    @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
mysql> SHOW triggers \G
*************************** 1. row ***************************
             Trigger: ins_transaction
               Event: INSERT
               Table: account
           Statement: SET
   @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
   @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0)
              Timing: BEFORE
             Created: 2014-05-14 21:23:49.66
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
             Trigger: ins_sum
               Event: INSERT
               Table: account
           Statement: SET @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2014-05-14 21:22:47.91
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

• Globalization improvements
• MySQL 5.7.4 includes a gb18030 character set that supports the China National Standard GB18030 character set.
• http://dev.mysql.com/doc/refman/5.7/en/charset-examples.html

mysql> CREATE TABLE t1
    -> (  c1 CHAR(10) CHARACTER SET latin1
    -> ) DEFAULT CHARACTER SET gb18030 COLLATE gb18030_chinese_ci;
Query OK


• Changing the replication master without STOP SLAVE
• http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
• Read more about this on via the two links above. This could be another very nice improvement. The documentation gives many examples.


• HANDLER with partitioned tables

mysql> HANDLER City OPEN AS city_handle;
mysql> HANDLER city_handle READ FIRST;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
|  1 | Kabul | AFG         | Kabol    |    1780000 |
+----+-------+-------------+----------+------------+
mysql> HANDLER city_handle READ NEXT LIMIT 3;
+----+-----------+-------------+---------------+------------+
| ID | Name      | CountryCode | District      | Population |
+----+-----------+-------------+---------------+------------+
|  5 | Amsterdam | NLD         | Noord-Holland |     731200 |
|  6 | Rotterdam | NLD         | Zuid-Holland  |     593321 |
|  7 | Haag      | NLD         | Zuid-Holland  |     440900 |
+----+-----------+-------------+---------------+------------+
mysql> CREATE TABLE `t2` (
    ->   `t2_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `inserttimestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ->   `somevalue` int(10) unsigned DEFAULT NULL,
    ->   `rowLastUpdateTime` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`t2_id`,`inserttimestamp`)
    -> ) ENGINE=InnoDB;
mysql> ALTER TABLE t2
    ->  PARTITION BY RANGE ( TO_DAYS(inserttimestamp) ) (
    ->      PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
    ->      PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
    ->      PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    ->      PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
    ->      PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
    ->      PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    ->      PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
    ->      PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
    ->      PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    ->      PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
    ->      PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
    ->      PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    ->      PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01'))
    ->  );
mysql> INSERT INTO t2 VALUES (NULL,NOW(),1,NOW());
mysql> HANDLER t2 OPEN AS t_handle;
mysql> HANDLER t_handle READ FIRST;
+-------+---------------------+-----------+---------------------+
| t2_id | inserttimestamp     | somevalue | rowLastUpdateTime   |
+-------+---------------------+-----------+---------------------+
|     1 | 2014-05-14 21:53:28 |         1 | 2014-05-14 21:53:28 |
+-------+---------------------+-----------+---------------------+


• Database name rewriting with mysqlbinlog 
• Renaming of databases by mysqlbinlog
• This is a feature that Percona has had for ages. 
• http://www.percona.com/doc/percona-server/5.1/release-notes/Percona-Server-5.1.62-13.3.html#5.1.62-13.3

mysql> select @@binlog_format\G
*************************** 1. row ***************************
@@binlog_format: ROW

# mysqlbinlog --database=world  mysql-bin.000002 | grep world | wc -l
22543# mysqlbinlog --rewrite-db='world->renameddb'  mysql-bin.000002 | grep renameddb | wc -l
22542

Can MySQL Replication catch up

So replication was recently improved in MySQL 5.6. However, people are still using 5.1 and 5.5 so some of those improvements will have to wait to hit the real world.

I recently helped move in this direction with a geo-located replication solution. One part of the country had a MySQL 5.1 server and the other part of the country had a new MySQL 5.6 server installed.

After dealing with the issues of getting the initial data backup from the primary to the secondary server (took several hours to say the least), I had to decide could replication catch up and keep up. The primary server had some big queries and optimization is always a good place to start. I had to get the secondary server pulling and applying as fast as I could first though.

So here are a few things to check and keep in mind when it comes to replication. I have added some links below that help support my thoughts as I worked on this.

Replication can be very I/O heavy. Depending on your application. A blog site does not have that many writes so the replication I/O is light, but a heavily written and updated primary server is going to lead to a replication server writing a lot of relay_logs and binary_logs if they are enabled. Binary logs can be enabled on the secondary to allow you to run backups or you might want this server to be a primary to others.

I split the logs onto a different data partition from the data directory.
This is set in the my.cnf file - relay-log

The InnoDB buffer pool was already set to a value over 10GB. This was plenty for this server.
The server was over 90,000 seconds behind still.

So I started to make some adjustments to the server and ended up with these settings in the end. Granted every server is different.

mysql> select @@sync_relay_log_info\G
*************************** 1. row ***************************
@@sync_relay_log_info: 0
1 row in set (0.08 sec)

mysql>  select @@innodb_flush_log_at_trx_commit\G
*************************** 1. row ***************************
@@innodb_flush_log_at_trx_commit: 2
1 row in set (0.00 sec)

mysql> select @@log_slave_updates\G
*************************** 1. row ***************************
@@log_slave_updates: 0

mysql> select @@sync_binlog\G
*************************** 1. row ***************************
@@sync_binlog: 0
1 row in set (0.00 sec)

mysql> select @@max_relay_log_size\G
*************************** 1. row ***************************
@@max_relay_log_size: 268435456

 I turned binary logging off as I monitored different settings and options to help replication catch up. It took a while. Some of the settings you see above may or may not have been applied as I worked within this time frame. Yet it did catch up to 0 seconds back. Now you might notice that a lot of these settings above relate in and around the binary logging.  So I ran a little test. So, I restarted and enabled the bin logs. I checked in on the server later and found it 10,000+ seconds behind.  So I once again restarted and disabled the bin logs.  It caught up (0 seconds behind) with the primary server in under 15 minutes. I used Aurimas' tool as I watched it catch up as well. If you have not use it before, it is a very nice and handy tool.

What this all means is the primary server must be ACID compliant. With this set up you are also depending on the OS for cache and clean up. This is server will be used as a primarily read server to feed information to others. It also means that yes, geo-located replication can stay up to date with a primary server.

What if you need to stop the slave, will it still catch up quickly?

How and why do you stop the slave is my first response.  You should get into the habit of using  STOP SLAVE SQL_THREAD; instead of STOP SLAVE;  This allows the relay logs to continue to gather data and just not apply it to your primary server.  So if you can take advantage of that it will help reduce the time it takes for you to populate the relay logs later. 

Some additional reading for you:

• http://www.mysqlperformanceblog.com/2011/07/29/reasons-for-mysql-replication-lag/
• http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/
• http://www.mysqlperformanceblog.com/2012/08/29/heres-a-quick-way-to-foresee-if-replication-slave-is-ever-going-to-catch-up-and-when/

MySQL access and replication blocked by secure_auth

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)


If you have tried to connect to a MySQL database and you see this error then you need to have valid 41byte hash password.  If you are unsure which you have execute the SQL below. If you have 16 character passwords they are older passwords.

select Password from mysql.user;


The following is how I solved this as part of a migration from MySQL 5.0 to MySQL 5.6.

The MySQL 5.0 server had a mixture of the older pre 4.1 passwords and valid 41byte passwords.  Because the MySQL 5.0 server had some accounts with the older passwords I decided to not dump the MySQL table as part of setting up replication. I did dump all of the databases except the mysql database. This allow ensured that I would keep the valid MySQL 5.6 table enhancements.

The MySQL 5.6 server installed easily and was up and I loaded the dump data.  Part of the migration was to use replication while they evaluated the new database. While on the MySQL 5.6 server I tested the replication user account. The response I got was the error at the top of this page. Replication will not run of course without a valid user account. This is why the error logs was giving me this error:

[ERROR] Slave I/O: error connecting to master '<user>@<hostname>:3306' - retry-time: 10  retries: 68, Error_code: 2049


A quick review of the account on the MySQL 5.0 server showed that the new account was established with the pre 4.1 password.  So I needed to upgrade the account to a valid 41 byte password.

The following query showed that they did indeed have old passwords enabled. So I have to disable that and update the user account again to set the password as a valid 41 byte hash.

>SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)


>SET @@session.old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)

>GRANT REPLICATION SLAVE ON *.* TO '<user>'@'<ip_address>' IDENTIFIED BY '<Password>';
Query OK, 0 rows affected (0.00 sec)


A check of the password showed the password as the 41byte password now. I was this able to connect to the primary server from the secondary server and avoid the secure_auth error. replication connected easily and problem was solved.

Going forward I needed to get the MySQL 5.0 users accounts onto the MySQL 5.6 server. ( since I skipped them as part of building the secondary server. )

The client needed to set the grants again for each user regardless of valid password or not.
So I instructed them to execute the following sql. I could have done this but I would need to know all of their passwords and that was not needed.

For each user in their system. You do not have to do the root user because you already have a valid root account on the 5.6 system.

>SET @@session.old_passwords = 0;
>show grants for '<User>'@'<Host>';


To gather the sql needed for each user run the following :

SELECT CONCAT("SHOW GRANTS FOR '",User,"'@'",Host,"';") as sql_command from mysql.user;


For each result given execute the "show grants" statement and then execute the statement given.
The statements should be similar to the following:

GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';


Replication then created and populated the MySQL table on the MySQL 5.6 server.

More can be found here:
http://dev.mysql.com/doc/refman/5.6/en/password-hashing.html

Also as an update --

Check out http://dev.mysql.com/doc/refman/5.6/en/account-upgrades.html

Create a Slave ( secondary) server with Percona Xtrabackup

So first you might just save yourself some time and read the Percona example for this:
http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/setting_up_replication.html

But just in case here is an example based on a real situation.

PRIMARY SERVER


# innobackupex /tmp/  <---- this is whatever directory you want to store the backup in. This is a very basic no fluff hot backup.

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
.........
130809 14:40:11  innobackupex: Connection to database server closed
130809 14:40:11  innobackupex: completed OK!


Make sure you see the xtrabackup_binlog_info file. If you do not you will not easily have the position and log information. You will have to dig into the binlogs based on time and etc. Which is more work than needed.


 innobackupex --apply-log /tmp/<Timestamp Directory Here>


Now up to you. You can rsync the directory to the slave or tar[gzip] then scp to the slave. Regardless of method to move to slave, you have a hotbackup created and ready to go.


SECONDARY SERVER


# /etc/init.d/mysql stop
mv /var/lib/mysql  /var/lib/mysql_ORIG


However you moved the file from the master to the slave, put the contents into the datadir folder, assumed for example: /var/lib/mysql .


# chown -R mysql:mysql mysql
 /etc/init.d/mysql start
Starting MySQL...                                          [  OK  ]

Now in your slave MySQL server, you can set the replication user information easily.


CHANGE MASTER TO
MASTER_HOST='<MASTER_HOST>',
MASTER_USER='<MASTER_USER>',
MASTER_PASSWORD='<MASTER_PASSWORD>',
MASTER_CONNECT_RETRY = 10 ;


Get the log and position from the xtrabackup file.


# more xtrabackup_binlog_info
<BinLog info> <POSITION INFO>

CHANGE MASTER TO MASTER_LOG_FILE='<BinLog info>', MASTER_LOG_POS=<POSITION INFO>;

Start slave;


That is it in a nutshell. For more information review the Percona url given at the start. 

MySQL < 5.5 replication to MySQL 5.6

After hours of frustration..... I will put it simply as do not upgrade to MySQL 5.6 if you are running any  version less that MySQL 5.5.

You have to upgrade to MySQL 5.5 first to keep your sanity and data in tact.

Lots of blog posts and information are available about the password changes in MySQL 5.6 and I support them. I even updated the MySQL 5.6 passwords and the box was up and running just fine. The problem was replication. I had to replicate from a MySQL version less than MySQL 5.5 and it simply would not run. I disabled secure_auth and could connect but still no luck with replication.

While I support replication as an upgrade path, take the time and stick with MySQL 5.5 first.

I ended up downgrading to MySQL 5.5 and everything runs just fine now. If you have to downgrade your MySQL version I had to follow many of the same steps I did in my Maria diaster post to get the box back up. 

Checking out MariaDB 10.0.2

I downloaded the MariaDB 10.0.2 source package and did a custom install.  I did this because of a previous post in which I had 2 masters already built. This time I removed the circular replication and pointed them to this mariadb install. I used port 3310 this time. Same install configuration examples from previous post would apply here just now put into mariadb-10.0.2 folders. I added the install at the bottom of this post just in case you want it.

The reason I did this was because I wanted to check out the latest MariaDB features primarily the following:

• Multi-source replication
• SHOW EXPLAIN

Multi-source replication

Make sure you have different server-ids set per server to start with.

Just started so nothing here should be expected

> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| |
+-----------------------------+


So gather information from a master server

> show master status\G
*************************** 1. row ***************************
File: percona_mysql-bin.000005
Position: 107


Now update the Mariadb 10.0.2 slave

SET @@default_master_connection='percona';

CHANGE MASTER 'percona' TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'root',
MASTER_PASSWORD = '',
MASTER_PORT = 3307 ,
MASTER_LOG_FILE = 'percona_mysql-bin.000005',
MASTER_LOG_POS = 107



> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| percona                     |
+-----------------------------+

OK Now let me add the second master

SET @@default_master_connection='oracle';

CHANGE MASTER 'oracle' TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'root',
MASTER_PASSWORD = '',
MASTER_PORT = 3309 ,
MASTER_LOG_FILE = 'oracle_mysql-bin.000009',
MASTER_LOG_POS = 5453


Next you can check the status to ensure both settings are set up.

>SHOW ALL SLAVES STATUS\G

*************************** 1. row ***************************
Connection_name: oracle
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3309
Connect_Retry: 60
Master_Log_File: oracle_mysql-bin.000009
Read_Master_Log_Pos: 5453
Relay_Log_File: relay-bin-oracle.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: oracle_mysql-bin.000009
Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5453
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: 0
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Pos:
*************************** 2. row ***************************
Connection_name: percona
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: percona_mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin-percona.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: percona_mysql-bin.000005
Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: 0
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Pos:


OK time to start it

> START ALL SLAVES;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

root@localhost [(none)]> show warnings;
+-------+------+-------------------------+
| Level | Code | Message |
+-------+------+-------------------------+
| Note | 1937 | SLAVE 'percona' started |
| Note | 1937 | SLAVE 'oracle' started |
+-------+------+-------------------------+



  Relay_Master_Log_File: percona_mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes




 Relay_Master_Log_File: oracle_mysql-bin.000009

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes




So let us test some situations.

Via Percona master

use test;
CREATE TABLE `multi_test` (
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


MariaDB Slave

> show tables;
+----------------+
| Tables_in_test |
+----------------+
| multi_test |
+----------------+


Via Oracle MySQL master

use test;
CREATE TABLE `multi_test2` (
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


MariaDB Slave

> show tables;
+----------------+
| Tables_in_test |
+----------------+
| multi_test |
| multi_test2 |
+----------------+


OK that works !


SHOW EXPLAIN
This is rather straight forward but nice to catch a query as it is running.

> show explain for 17;
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | sbtest | range | PRIMARY | PRIMARY | 4 | NULL | 99 | Using where |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [test]> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------+
| Note | 1003 | SELECT SUM(K) from sbtest where id between 4997 and 5096 |
+-------+------+----------------------------------------------------------+


Side notes:


Cassandra Storage Engine
I am curious about this and how it relates to the NoSQL and Innodb solution via memcache.
I have a post on that here: http://anothermysqldba.blogspot.com/2013/04/nosql-php-memcache-innodb-mysql.html

I will have to come back to this as I set up Cassandra on my environment. I am not eager but curious.


User Feedback Plugin
The documentation "Quick start" says to add to the my.cnf file under [mysqld]

[mysqld]
feedback=ON
port = 3310
socket = /tmp/mariadb-10.0.2.sock

130513 17:45:10 InnoDB: 10.0.2-MariaDB started; log sequence number 20183690
130513 17:45:10 [ERROR] /usr/local/mariadb-10.0.2/bin/mysqld: unknown variable 'feedback=ON'


This worked a lot easier and as expect this way once I removed the "Quick start" instructions.

> INSTALL PLUGIN feedback SONAME 'feedback.so';

> SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'feedback';
+---------------+
| plugin_status |
+---------------+
| ACTIVE |
+---------------+


Via the error log you can also see it working:

[Note] feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent
[Note] feedback plugin: server replied 'ok'

Overall my current favorite enhancements are :

• Multi-source replication
• SHOW EXPLAIN

The basic install was this:

# Preconfiguration setup
shell> groupadd mariadb
shell> useradd -r -g mariadb mariadb

# Beginning of source-build specific instructions
shell> tar zxvf MariaDB-VERSION.tar.gz
shell> cd MariaDB-VERSION
shell> cmake .
shell> make
shell> make install DESTDIR="/usr/local/mariadb-10.0.2-tmp"
# End of source-build specific instructions

Build files have been written to: /usr/local/src/MySQL/MariaDB/10.0.2/mariadb-10.0.2

I do not like the results
-- Installing: /usr/local/mariadb-10.0.2-tmp/usr/local/mysql/
If DESTDIR is should install into that location not start with user under that location. This is a MySQL original issue as it does this with all versions of MySQL.

# Fix the odd/bug setup
shell> cd /usr/local/mariadb-10.0.2-tmp
shell> mv usr/local/mysql/ ../mariadb-10.0.2 ;
shell> cd ../; # rm -Rf mariadb-10.0.2-tmp

# Postinstallation setup
shell> cd /usr/local/mariadb-10.0.2
shell> chown -R mariadb .
shell> chgrp -R mariadb .

# Next command is optional
shell> cp support-files/my-small.cnf /etc/mariadb-10.0.2.cnf
shell> vi /etc/mariadb-10.0.2.cnf
port = 3310
socket = /tmp/mariadb-10.0.2.sock

shell> scripts/mysql_install_db --defaults-file=/etc/mariadb-10.0.2.cnf --basedir=/usr/local/mariadb-10.0.2 --skip-name-resolve --datadir=/var/lib/mariadb-10.0.2 --user=mariadb
shell> chown -R mariadb /var/lib/mariadb-10.0.2/*

shell> # bin/mysqld_safe --defaults-file=/etc/mariadb-10.0.2.cnf --user=mariadb --datadir=/var/lib/mariadb-10.0.2/ --port=3310 &


shell> # ./bin/mysql --port=3310 --socket=/tmp/mariadb-10.0.2.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.2-MariaDB Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.