MYSQL DBA: May 2020

Sunday, May 24, 2020

How to install mysql and configure SSL?

A MySQL client can establish an encrypted connection to a MySQL server. In standard configuration, a client connection is unencrypted, which can lead to data being intercepted on the way. The MySQL encryption can be done separately for each client connection, so both encrypted and unencrypted connections can be used simultaneously. It can also be configured as required for individual connections.

mysql> show variables like '%ssl%';
+---------------+----------------------------+
| Variable_name | Value |
+---------------+----------------------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | /etc/mysql/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/mysql/server-key.pem |
+---------------+----------------------------+
9 rows in set (0.02 sec)

how to enable ssl

step.1.

Download and install the required mysql related packages

yum install mysql-server perl-DBD-MySQL perl-DBI

step.2.

create a directory

[root@node2 ~]# mkdir -p /home/mysql/certs/

step.3.

You need to issue certificates with 2048 bits and a validity of 3650 days. After this period, the certificates must be renewed or recreated. Depending on your requirements, you might lower the time frame for validity.

# Generate CA file

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

# Generate server certifacte

openssl req -newkey rsa:2048 -days 3560 -nodes -keyout server-key.pem > server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Generate client certificate

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

# Verify certificates

openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

step.4.

[root@node2 certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

server-cert.pem: OK

client-cert.pem: OK

step.5 .Add the following into the "/etc/my.cnf" file, under the "[mysqld]" section.

[root@node2 certs]# vi /etc/my.cnf

[mysqld]

#SSL

#ssl=1

ssl-ca=/home/mysql/certs/ca-cert.pem

ssl-cert=/home/mysql/certs/server-cert.pem

ssl-key=/home/mysql/certs/server-key.pem

step.7.Add in the following client section to the "/etc/my.cnf" file.

[root@node2 certs]# vi /etc/my.cnf

[client]

ssl-ca=/home/mysql/certs/ca-cert.pem

ssl-cert=/home/mysql/certs/client-cert.pem

ssl-key=/home/mysql/certs/client-key.pem

step.8. Restart the mysqld service.

[root@node2 certs]# systemctl restart mysqld

step.9.login mysql and check ssl and have_ssl variable

[pankaj@node2 certs]$ mysql --ssl-cert=ca-cert.pem --ssl-key=client-key.pem --ssl-cert=client-cert.pem -u root -p -v -v -v

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 262

Server version: 5.7.30-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Reading history-file /home/pankaj/.mysql_history

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl conection via unix socket. Please consider

switching ssl off as it does not make connection via unix socket

any more secure.

mysql>

[root@node3 ~]# mysql -u db_user -h 192.168.15.227 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 49

Server version: 5.7.30-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> show variables like '%ssl%';

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

| Variable_name | Value |

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

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /home/mysql/certs/ca-cert.pem |

| ssl_capath | |

| ssl_cert | /home/mysql/certs/server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | /home/mysql/certs/server-key.pem |

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

9 rows in set (0.00 sec)

mysql> show variables like '%have_ssl%';

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

| Variable_name | Value |

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

| have_ssl | YES |

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

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Ssl_cipher';

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

| Variable_name | Value |

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

| Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 |

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

1 row in set (0.02 sec)

step.9.

User Creation

Once SSL is configured, any connection to MySQL can optionally choose to use SSL or X509. The use of SSL can be forced using REQUIRE. Using REQUIRE SSL means the client must have access to the "ca-cert.pem" certificate. Using "REQUIRE X509" means the client also needs access to the client certificate and key. We can test this using the following users.

CREATE DATABASE IF NOT EXISTS pankaj DEFAULT CHARACTER SET utf8;

CREATE USER 'ssl_user'@'%' identified 'Ssluser@123';

GRANT ALL PRIVILEGES ON pankaj.* TO 'ssl_user'@'%' REQUIRE SSL WITH GRANT OPTION;

FLUSH PRIVILEGES;

HOW TO RECOVER PERFORMANCE _SCHEMA DATABASES

ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist

STEP.1 UPDATE MYSQL.

[root@node2 ~]# mysql_upgrade -u root -p --force
Enter password:
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

sys.sys_config OK

Upgrade process completed successfully.

Checking if update is needed.

STEP.2.

Restart mysqld service

[root@node2 ~]# systemctl restart mysqld

STEP.3.Login Mysql

[root@node2 ~]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.30-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

7 rows in set (0.00 sec)

after that checking variable

mysql> show variables like '%ssl%';

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

| Variable_name | Value |

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

| have_openssl | DISABLED |

| have_ssl | DISABLED |

| ssl_ca | /etc/mysql/ca-cert.pem |

| ssl_capath | |

| ssl_cert | /etc/mysql/server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | /etc/mysql/server-key.pem |

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

9 rows in set (0.04 sec)

Friday, May 22, 2020

Mysqladmin Commands for MySQL Database Administration in Linux

mysqladmin is a utility the command-line comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.

In this article we’ve compiled some very useful ‘mysqladmin‘ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks.

1. How to set MySQL Root password?

If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command.

# mysqladmin -u root password YOURNEWPASSWORD

2. How to Change MySQL Root password?

If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with new password say xyz123.

#mysqladmin -u root -p123456 password 'xyz123'

3. How to check MySQL Server is running?

To find out whether MySQL server is up and running, use the following command.

[root@node2 ~]# mysqladmin -u root -p ping

Enter password:

mysqld is alive

4. How to Check which MySQL version I am running?

The following command shows MySQL version along with the current running status .

[root@node2 ~]# mysqladmin -u root -p version

Enter password:

mysqladmin Ver 8.42 Distrib 5.7.28, for Linux on x86_64

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Server version 5.7.28-log

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/lib/mysql/mysql.sock

Uptime: 1 day 10 hours 4 min 32 sec

Threads: 2 Questions: 351010 Slow queries: 0 Opens: 18011 Flush tables: 1 Open tables: 442 Queries per second avg: 2.861

5. How to Find out current Status of MySQL server?

To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries

[root@node2 ~]# mysqladmin -u root -p status

Enter password:

Uptime: 123117 Threads: 2 Questions: 352587 Slow queries: 0 Opens: 18095 Flush tables: 1 Open tables: 442 Queries per second avg: 2.863

6. How to check status of all MySQL Server Variable’s and value’s?

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.

[pankaj@node3 /]$ mysqladmin -u root -p extended-status

Enter password:

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

| Variable_name | Value |

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

| Aborted_clients | 3 |

| Aborted_connects | 3 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 0 |

| Bytes_received | |

| Bytes_sent | |

| Com_admin_commands | 0 |

| Com_assign_to_keycache | 0 |

| Com_alter_db | 0 |

| Com_alter_db_upgrade | 0 |

| Com_alter_event | 0 |

| Com_alter_function | 0 |

| Com_alter_procedure | 0 |

| Com_alter_server | 0 |

| Com_alter_table | 0 |

| Com_alter_tablespace | 0 |

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

7. How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.

[pankaj@node3 /]$ mysqladmin -u root -p variables

Enter password:

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

| Variable_name | Value |

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

| auto_increment_increment | 1 |

| auto_increment_offset | 1 |

| autocommit | ON |

| automatic_sp_privileges | ON |

| back_log | 50 |

| basedir | /usr |

| big_tables | OFF |

| binlog_cache_size | 32768 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_format | STATEMENT |

| binlog_stmt_cache_size | 32768 |

| bulk_insert_buffer_size | 8388608 |

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

| collation_connection | latin1_swedish_ci

8. How to check all the running Process of MySQL server?

The following command will display all the running process of MySQL database queries.

[pankaj@node3 /]$ mysqladmin -u root -p processlist

Enter password:

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

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

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

9. How to create a Database in MySQL server?

To create a new database in MySQL server, use the command as shown below.

[pankaj@node3 /]$ mysqladmin -u root -p create pankaj

Enter password:

[pankaj@node3 /]$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 25

Server version: 5.7.28-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

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

| Database |

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

| information_schema |

| logviewer |

| mysql |

| pankaj |

| performance_schema |

| sys |

| target |

| zabbix_failover |

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

8 rows in set (0.13 sec)

10. How to drop a Database in MySQL server?

To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y‘.

[pankaj@node3 /]$ mysqladmin -u root -p drop pankaj

Enter password:

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the 'pankaj' database [y/N] y

Database "pankaj" dropped

11. How to reload/refresh MySQL Privileges?

The reload command tells the server to reload the grant tables. The refresh command flushes all tables and reopens the log files.

# mysqladmin -u root -p reload;

# mysqladmin -u root -p refresh

12. How to shutdown MySQL server Safely?

To shutdown MySQL server safely, type the following command.

mysqladmin -u root -p shutdown

Enter password:

You can also use the following commands to start/stop MySQL server.

# /etc/init.d/mysqld stop

# /etc/init.d/mysqld start

13. Some useful MySQL Flush commands

Following are some useful flush commands with their description.

flush-hosts: Flush all host information from host cache.

flush-tables: Flush all tables.

flush-threads: Flush all threads cache.

flush-logs: Flush all information logs.

flush-privileges: Reload the grant tables (same as reload).

flush-status: Clear status variables.

# mysqladmin -u root -p flush-hosts

# mysqladmin -u root -p flush-tables

# mysqladmin -u root -p flush-threads

# mysqladmin -u root -p flush-logs

# mysqladmin -u root -p flush-privileges

# mysqladmin -u root -p flush-status

14. How to kill Sleeping MySQL Client Process?

Use the following command to identify sleeping MySQL client process.

# mysqladmin -u root -p processlist

Enter password:

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

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

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

Now, run the following command with kill and process ID as shown below.

# mysqladmin -u root -p kill 5

Enter password:

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

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

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

If you like to kill multiple process, then pass the process ID‘s with comma separated as shown below.

# mysqladmin -u root -p kill 5,10

15. How to run multiple mysqladmin commands together?

If you would like to execute multiple ‘mysqladmin‘ commands together, then the command would be like this.

# mysqladmin -u root -p processlist status version

Enter password:

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

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

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

Uptime: 3801 Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.003

mysqladmin Ver 8.42 Distrib 5.5.28, for Linux on i686

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Server version 5.5.28

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/lib/mysql/mysql.sock

Uptime: 1 hour 3 min 21 sec

Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8

16. How to Connect remote mysql server

To connect remote MySQL server, use the -h (host) with IP Address of remote machine.

# mysqladmin -h 172.16.25.126 -u root -p

17. How to execute command on remote MySQL server

Let’s say you would like to see the status of remote MySQL server, then the command would be.

# mysqladmin -h 172.16.25.126 -u root -p status

18. How to start/stop MySQL replication on a slave server?

To start/stop MySQL replication on salve server, use the following commands.

# mysqladmin -u root -p start-slave

# mysqladmin -u root -p stop-slave

19. How to store MySQL server Debug Information to logs?

It tells the server to write debug information about locks in use, used memory and query usage to the MySQL log file including information about event scheduler.

# mysqladmin -u root -p debug

Enter password:

20. How to view mysqladmin options and usage

To find out more options and usage of myslqadmin command use the help command as shown below. It will display a list of available options.

# mysqladmin --help

Tuesday, May 19, 2020

HOW TO INCREASE AND DECREASE MYSQL MAX_CONNECTION

Step.2.suppose you need 500 user at a time connect in mysql database then you need to increase the no of connection or max_connection variable.

There are two way to increase and decrease connection:-

1.Temporary

2.parmament.

1.you need to login mysql as root user and set max_connection variable.

mysql> set global max_connections = 500;

Query OK, 0 rows affected (0.00 sec)

Important thing you reboot the system your max_connection as well as previous state.

mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)

2.you can increase parmament connection then you can entry in my.cnf file under mysqld section.

vi /etc/my.cnf
[mysqld]

max_connection = 500

then restart mysqld servervice

systemctl restart mysqld

step.3.after that you can check mysql connection

mysql> show variables like '%max_connection%';

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

| Variable_name | Value |

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

| max_connections | 500 |

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

1 row in set (0.00 sec)

Thank You.

Monday, May 18, 2020

HOW TO RECREATE NEW SLAVE SAME SERVER IN MYSQL REPLICAITON

step :1.you are not changing any parameter file both master and slave server my.cnf file.

MASTER SERVER:-

step.2.you can take full backup in master server.

mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user='username' --p'passwd' > mysqldump.sql;

step.3.you can transfer backup .sql file master server to slave server.

scp mysqldump.sql ram@'x.x.x.x':.

note:-
ram is a user name your slave server and (x.x.x.x) is your ip for slave machine.

SLAVE SERVER:-

step.4.First login mysql in slave server.
mysql -u root -p
password:-'enter user passwd'

step.5.first stop slave server and reset all command.

mysql> stop slave;

mysql> reset slave all;

step.5.after that you are reset master command in slave server.

mysql>reset master;

step.6.you can also check it both variable gtid_executed and gtid_purged not show any gtid.

mysql> show global variables like '%gtid_executed%';

mysql> show global variables like '%gtid_purged%';

step.7.Restore backup file mysqldump.sql.

mysql>source mysqldump.sql;

step.8.now again you can check it gtid_executed and gtid_purged .you can see a gtid values.

mysql> show global variables like '%gtid_executed%';

mysql> show global variables like '%gtid_purged%';

step.9.last and fimnal step.

MYSQL>CHANGE MASTER TO
MASTER_HOST = 'X.X.X.X',
MASTER_PORT = 3306,
MASTER_USER = 'XXXX,
MASTER_PASSWORD = 'XXXXX',
MASTER_AUTO_POSITION = 1;

step.9.you can completely set slave server and you can check status for slave server.

mysql > show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Thank You.

Friday, May 15, 2020

ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty

mysql>stop slave;
mysql> reset master;
mysql > show global variables like '%GTID_EXECUTED%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_executed | |
+---------------+-------+
mysql > show global variables like '%GTID_PURGED%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_purged | |
+---------------+-------+
mysql > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14";

mysql> show global variables like 'gtid_executed';
+---------------+-------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------+
| gtid_executed | 16a931e0-b829-11e9-b423-0cc47ab45dc4:1-46251417 |
+---------------+-------------------------------------------------+
1 row in set (0.01 sec)

mysql> show global variables like 'gtid_purged';

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

| Variable_name | Value |

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

| gtid_purged | 16a931e0-b829-11e9-b423-0cc47ab45dc4:1-46102650 |

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

1 row in set (0.01 sec)

mysql> start slave io_thread;
mysql> show slave status\G

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: x.x.x.x
Master_User: user_name
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000248
Read_Master_Log_Pos: 713228337
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 713215869
Relay_Master_Log_File: mysql-bin.000248
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mamcdb_cloud.mamdatalist
Replicate_Wild_Do_Table: mamcdb_cloud.%,aom.%,mediaxdb.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 713228337
Relay_Log_Space: 713216076
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
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: 1
Master_UUID: 16a931e0-b829-11e9-b423-0cc47ab45dc4
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 16a931e0-b829-11e9-b423-0cc47ab45dc4:46102667-46252600
Executed_Gtid_Set: 16a931e0-b829-11e9-b423-0cc47ab45dc4:1-46252600
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

SKIP SLAVE COUNTER WITH GTID_MODE ON #ERROR 1858 (HY000)

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON.

With generic asynchronous MySQL Replication we often use skip_slave_counter to tackle / skip anonymous transactions which creates errors or halt MySQL Replication. But if we try to skip slave counter whilst GTID_MOD is ON we get following error.

In this case we should do following steps

Stop Replication.
.Set GTID_MODE to ON_PERMISSIVE
Skip slave counter.
Start slave.
Set GTID_MODE to ON once it catches the master.
GTID_MODE is dynamic variable which can be set on the run. Here we need to look at GTID_MODE following options.

OFF: Both new and replicated transactions must be anonymous.
OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
ON: Both new and replicated transactions must be GTID transactions.
All these steps are shown below

mysql> stop slave;
mysql> set global gtid_mode=ON_PERMISSIVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
### Once caught up set it back
mysql> set global gtid_mode=ON;

MySQL Replication skip error

Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table mamc.id_findloc; Can't find record in 'id_findloc', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log 1.000221, end_log_pos 2256175

You now need to know which exact GTID needs to skip to start the replication again. You see at the output

Retrieved_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:183900240-183900398:188376296-241806257

It means Slave retrieved the last GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257.

The next line of the output

Executed_Gtid_Set: 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:1-234830504

It means Slave could execute GTIDs upto 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830504. So GTIDs 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505 to 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:241806257 are retrieved but pending to be executed.

That means it stopped during execution of GTID 8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505(one greater than last executed). So we need to skip this GTID to be executed and this is called “MySQL Replication skip error” tricks. Follow the step by step procedure to apply “MySQL Replication skip error” technique

Stop The slave

mysql> stop slave;

Set global GTID_NEXT variable to intended GTID to be skipped

mysql> SET GTID_NEXT='8c3cb328-b0b1-11e2-bdb6-3440b5acc06c:234830505';

Execute an empty transaction

mysql> BEGIN;

mysql> COMMIT;

Set global GTID_NEXT variable to AUTOMATIC again

mysql> SET GTID_NEXT='AUTOMATIC';

Start Slave again

mysql> start slave;

Check Slave status again

mysql> show slave status\G

Replication handler error HA_ERR_KEY_NOT_FOUND and Memory Tables In Replication.

Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table mamc.id_findloc; Can't find record in 'id_findloc', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log 1.000221, end_log_pos 2256175

This is one of the error for MySQL Replication when MASTER and SLAVE replica not in sync. The Reason for replication getting out of sync due to various reasons. But the common cause is particular table row or data not matched/missing on the slave while applying transaction received from MASTER. This transaction can be insert/update/delete type.

In this blog post will discuss support for MEMORY storage engine table with MySQL Replication and few examples with a possible solution. As per official MySQL DOC, support for replication with memory storage engine is “LIMITED”.

The reason is limited because of the MEMORY storage engine functionality, which stores all data in memory other than table metadata and structure. And this data will get flushed from the memory at the time of MySQL server restart.

Possible Solutions to bring backup replication in sync: [ Without downtime]

Option 1: Set slave_exec_mode to IDEMPOTENT It will suppress duplicate-key and no-key-found errors. Default slave_exec_mode is STRICT means no such suppression takes place.

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

mysql> Set global slave_exec_mode=IDEMPOTENT;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Option 2: Skip memory tables from replication using replication filter “replicate_ignore_table”. From 5.7.3 we can set replication filters dynamically without mysql service restart.

Option 3: If you want to keep data persistently then better to covert this table from MEMORY to InnoDB storage engine type using ALTER TABLE… ENGINE=Innodb command.

Selecting one of the options is depending on what we really need. For example, rather than skipping transaction one by one we can set Set slave_exec_mode to IDEMPOTENT for the time being to get replication in sync and after that set it back to STRICT again.