Linux Scripts to Create Table Partition for Next Month Automatically

 In this article we are going to show you a method of create new monthly partition for MySQL table without stored procedure, with the REORGANIZE PARTITION from MySQL/MariaDB.

(Click here to skip the article and straight to the scripts.)

For demo purpose, below are the DML of the table to be partitioned, and the ALTER statement to start with 2 partitions: "p_old" and "p_future". Please change the p_old partition date to the first of next month.

Below is the template of ALTER statement to REORGANIZE PARTITION "p_future" into a partition of next month ('p_202109' for example, as of writing), and 'p_future' for records after next month.

And here is the shell script that did some simple string substitute based on the template, and execute it on your MySQL/MariaDB server. Please do not forget to change the username, password and database name to match you database server.

Finally, you may run this each month to create a new partition for next month with command:

./create_partition.sh

If you are to schedule it via crontab, ensure you modify the script to use full path for the template file.

Thank you.

MySQL Random Words Based on Lorem Ipsum Text

Below is a MySQL function that create random text based on 100 words from Lorem Ipsum text.

ERROR 1 (HY000) at line 1: Can't create/write to file

MySQL SELECT statement allowed us to pipe the query result in to a file via SELECT ... INTO OUTFILE. It work just fine for me most of the time, until recently I hit this error:

ERROR 1 (HY000) at line 1: Can't create/write to file '\home\myuser\my_output_file.txt' (Errcode: 2) mv: cannot stat `/home/myuser/my_output_file.txt': No such file or directory

The first thing come to my mind is the problem of file permission, so I grant 777 permission to my output directory. But the problem still exist.

Then I check the Mysql username I use, and found that it was granted with FILE permission correctly.

Then I start to google on this error code, and most of the search result point to similars causes.

So I read again the documentation of MySQL, and I found this:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.

And the keyword is "server host". I made wrong assumption that it will write file to the server that execute the SELECT ... INTO OUTFILE.

And the workaround is simple, just change my SELECT statement by remove the OUTFILE portion. And pipe the query to file from the MySQL command line

mysql -u myuser -ppassword -h remotehost mydb < myquery.sql > my_output_file.txt

Reading documentation is bored but important :P

MySQL: Limit Relay Log Size To Prevent Hard Disk Full

In MySQL replication, you may notice the slave host generate some file with filename like 'hostname-relay-bin.000001' in data directory.

These files are the relay log generated by MySQL slave to store the replication data from master host before update into slave DB.

So when replication was lag behind master, or replication was stopped for long time and resume, large relay log will be generated by MySQL slave.

And this may cause your hard disk full, and result in MySQL stop working.

To prevent this, you can set the 'relay-log-space-limit' in your my.ini file (i.e. 5G):

relay-log-space-limit=5G

Regarding the appropriate size, here is the suggestion from MySQL documentation:

You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to temporarily ignore --relay-log-space-limit.

In case this long description make you blur, just set the 'relay-log-space-limit' to 3 times the value of 'max-binlog-size' or 'max-relay-log-size'.

MySQL: continue auto increment number from deleted record

The behavior of auto increment number in MySQL is keep increasing even the existing record had been removed. For example, let say your auto increment had reach 10, and you delete the record of 9, 10. The next record inserted is will still be 11.

Sometime you will just want it to be continue with 9 instead of 11. It can be done with an ALTER TABLE statement:

ALTER TABLE tablename AUTO_INCREMENT = 1;

MySQL Problem: Changes in my.ini do not reflect in replication

In order to setup replication, we may configure the master host details in slave DB 'my.ini' file. Example of configuration is as below:

master-host=10.0.0.112
master-port=3306
master-user=repl_user
master-password=password123
master-connect-retry=60
report-host=slaveHostName

However, after replication started, if you change the master host information in my.ini, it will not take effect after you restart the slave, not even you restart your MySQL.

This is because once MySQL replication started, it will generate a file 'master.info' in MySQL data folder. If this file was found when starting replication slave, replication will start based on 'master.info' and ignore the replication options configure in 'my.ini'.

So besides changing the 'my.ini', you should also issue a CHANGE MASTER TO command.

The steps are:
i) stop slave;
ii) CHANGE MASTER TO ...
iii) start slave.;

Host Blocked when conenct to MySQL

Host will be blocked when then number of interrupted connection attempt is more than the system variables of "max_connect_errors". In order to unblock the host, we may flush the hosts table in mysql database.

You may either flush hosts by mysqladmin:

mysqladmin -u username -ppassword flush-hosts

or by SQL:

FLUSH HOSTS;

To increase the "max_connect_errors", simply execute this:

set @@global.max_connect_errors=10000;

Appreciate if you would like to share some guide on steps to troubleshoot the interrupted connect request. Thanks in advance.

Purge MySQL Binlog Safely

In order to setup MySQL replication, binlog must be enabled at master database. However, MySQL do not cleanup binlog generated automatically, and while you discover your server run out the disk space, the most common reaction is delete the binlog file from harddisk manually.

However, this is somehow risky as it do not update the *.index (for example, mydb-bin.index if the binlog is set as 'mydb-bin') file.

Fortunately MySQL do provide a way to handle these binlog - PURGE MASTER LOGS.

For example: If you issues command below:

PURGE MASTER LOGS TO 'mydb-bin.0023';

Then the binlog before 'mydb-bin.0023' will be deleted.

You may also do this:

PURGE MASTER LOGS BEFORE '2008-06-13 08:00:00'

All binlog prior to the date will be deleted.

MySQL Replication Problem: MySQL 4.1 to MySQL 5.0

Recently had setup a MySQL 5.0 server as slave that connect to an existing MySQL 4.1 master server. Then found that the replication is stopped due to errors on many SQL statements.

The statement that give error include a insert statement that insert empty string into an integer field that have default value of zero, with error number 1366, incorrect integer value.

However, these statements are worked in MySQL 4.0. And when I execute them on MySQL 5.0 via my favorite MySQL client software SQLYog, it is worked too.

After some google, I found that this is cause by in MySQL 5.0 had enable strict mode by default.

So I disable the strict sql mode by remove the entry below from my.ini:

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And the replication goes happily ever after. :D

Solution to MySQL Replication Problems: CREATE USER failed

After setup replication, I start to create a few database login. And the next day, I found that the database replication is stopped. The error code is 1396 which indicate the failure of create user.

However, when i checked into the database, the database login is created successfully. I restart the slave a few time but replication still failed to start.

Then I google and found this solution.

The workaround is add the following line into my.cnf, then restart the slave database:

slave_skip_errors=1396

Solution to MySQL Replication Problems: Master DB crashed

It is commons that replication will hang after master database crashed. And the error message you will get when you show slave status is:

Client requested master to start replication from impossible position (server_errno=1236)

This happened because MySQL write to bin-log in batch basis. If every bit of your data is mission critical and any inconsistent between master and salve is not accectable, then you should redo you replication all over again.

Else, there is another way to resume the replication. The solution is illustrate in this article: Client asks master to start replication from impossible position

So far I do not found any way to prevent this replication failure. However, you may add the follwing line into you my.cnf to reduce the chances it happen:

sync_binlog=1

This means that MySQL will write to bin-log after each update instead of write in batch. As you may expect, this will reduce the performance of the database.