Posts tagged MySQL

percona_logo

MySQL – Percona – Release Notes – Identifying Changes Between Versions

0

I run Percona MySQL on all my CentOS servers by way of the Percona MySQL RPM. So every time I run `yum update` Percona MySQL also gets updated. Unfortunately, when yum reports back with the packages that will be updated, and Percona MySQL is among the list, all I see is a version number. Rather than blindly accepting the package upgrade, I would also like to see the change log, so I had to do some searching but found the 5.1, and 5.5 release notes. Posting links so others can easily access the release notes every time a new release makes it way through a yum update.

logo-mysql-110x57

MySQL – Enable MySQL Query Logging

0

Recently I wanted to track all the queries being executed for one of my applications, and found a great way to turn on query logging. I would then tail the log file and run through my app to see which queries were actually being executed. My app is based on doctrine (1.2) and I was amazed at how many queries were being executed, but I will save that for another article.


set global log_output = 'FILE';

set global general_log = 'ON';

set global general_log_file = '/var/log/mysql/queries.log';

Linux – CentOS – 6 – Installing Percona MySQL

0

If you are looking to install Percona MySQL on a CentOS 6 server, you will need to install the ‘Percona-Server-shared-compat.x86_64′ package, if you don’t, you will end up with errors when installing the ‘Percona-Server-server-55.x86_64′ package.

MySQL – Percona – PID File Could not be Found

2

If you ever come across a “MySQL (Percona Server) PID file could not be found” message when trying to restart, or stop your MySQL server there is an easy fix. Simply check the current running processes and get the PID of the MySQL process, then echo it into the pid file.

Example:


$ -> ps -auxfw | ack mysql

mysql    26519  0.1 23.2 755928 101344 pts/1   Sl   05:51   0:00  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/lib/mysql/myhost.pid --socket=/var/lib/mysql/mysql.sock --port=3306

$ -> echo 26519 > /var/lib/mysql/myhost.pid

$ -> /etc/init.d/mysql stop

/etc/init.d/mysql stopShutting down MySQL (Percona Server)....                   [  OK  ]

Now you should be able to start your MySQL server like normal.

MySQL – InnoDB Error Log File is of Different Size

2

There may be times where you will have to adjust the size of MySQL InnoDB log files to increase performance and stability, but take caution when you do, otherwise you will end up in a world of hurt. If you change the size of the log file and attempt to restart and get a “InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes” error message, you will need to follow the steps outlined @ http://www.mysqlperformanceblog.com/2011/07/09/how-to-change-innodb_log_file_size-safely/ to ensure no data loss or corruption.

MySQL – Percona – Setting Character Sets and Collations to UTF8

0

This morning I was reinstalling Percona-Server-server (v5.5.13) via Yum on my CentOS box and decided to dig into the my.cnf file a bit and make sure I had everything setup correctly. Lo and behold, I did not. I noticed that a few of my charset and collation settings were using latin1 when they should have been using UTF8.

Being a DBA newb, I tried to set server variables like ‘character_set_database’ only to see the following error messages: “110726 20:24:04 [ERROR] /usr/sbin/mysqld: unknown variable ‘character_set_database=utf8′”. I read the docs on mysql.com @ http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_database and couldn’t see what the problem was. It was listed as a ‘Global’ option, however if you scroll up to “Table 5.2″ you will notice that character_set_database cmd-line option is blank, which I assume means it cannot be set via the my.cnf file. I believe this setting can be set during ‘CREATE DATABASE’ commands, but I didn’t want to have to do this every time I created a database.

So I did some toying around and found the ‘character_set_server’ and ‘collation_server’ settings will actually control the children settings (database, table, etc). So all you have to do to ensure consistent utf8 for your mysql server is to add the following to your my.cnf file:


character_set_server = utf8
collation_server = utf8_general_ci

Be sure to restart the daemon, then you can issue the following commands:


# Show me collation settings

mpurcell@dev1 ~ $ -> mysql -e "show variables" -u mpurcell -p | fgrep -i collat
Enter password:
collation_connection    utf8_general_ci
collation_database      utf8_general_ci
collation_server        utf8_general_ci

# Show me charset settings
mpurcell@dev1 ~ $ -> mysql -e "show variables" -u mpurcell  -p | fgrep -i char
Enter password:
character_set_client    utf8
character_set_connection    utf8
character_set_database    utf8
character_set_filesystem    binary
character_set_results    utf8
character_set_server    utf8
character_set_system    utf8
character_sets_dir    /usr/share/mysql/charsets/

Now your mysql server is setup to store and collate in UTF8.

MySQL – Remove auto_increment from Schema Dumps (mysqldump)

2

I recently upgraded from MySQL 5.0.x to 5.5.x and ran into a weird issue where mysqldump command would also include the auto_increment in the resulting .sql. This isn’t an issue if you are dumping data, but if you are dumping only schema then this is a problem b/c any new entries into the table will start at whatever value the auto_increment value is, not the idea situation. You can read more about the bug @ http://bugs.mysql.com/bug.php?id=20786.

A user with the name Richard Fearn posted a fix for removing auto_increment references from the .sql file:

sed 's/ AUTO_INCREMENT=[0-9]*\b//'

So the full mysqldump cli command would look like:

mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > <filename>.sql

I’ve tried this command and works well.

MySQL – Rename Column

0
ALTER TABLE `table_name` CHANGE `old_col_name` `new_col_name` `col_data_type`;

MySQL – Using Interval to set Dates

1

There may be instances where you need to update several row date columns with one query. For example, if you need to extend out an ‘expire_date’ column by 30 days. Example:


UPDATE <some_table> SET expire_date = adddate(now(), interval 30 DAY) WHERE <some_criteria>;

You can find more MySQL date functions @ http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

MySQL – Stored Routines (Procedures)

0

A living document covering MySQL stored routine aka procedures functionality.

Check which routines are stored:

select * from information_schema.ROUTINES

Execute a stored procedure:

call stored_procedure_name(arg1, (arg2...));

Add a stored procedure:

Best way to add a stored procedure is to create a .sql file with the contents of stored procedure then call mysql from shell cli.

mysql -u <user> -h <db-host> -p < /path/to/.sql

    
Go to Top