Posts Categorized: mysql

MySQL Proxy

Today I wanted to create a straight-forward MySQL Proxy service which would forward MySQL queries from a development machine to another live server which was running MySQL. The aim was to allow people on the development machine to use the same connection string as on the live server, specifying the MySQL host as localhost but… Read more »

MySQL query output to text or CSV

I needed to output the results of a MySQL query today for further analysis and found the answer here. For future reference… SELECT * FROM errors WHERE mailfrom LIKE ‘a.n.other%’ INTO OUTFILE ‘/tmp/a.n.other.txt’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’; It is also possible to enclose the fields with quotes using: ENCLOSED BY ‘”‘

MySQL: Too Many Connections

On a shared server with MySQL and Apache the MySQL server was periodically disallowing connections as there were already too many connections. The default is 150+1 so I added the following to /etc/my.cnf max_connections=200 To increase the maximum number of connections to 200+1

Set MySQL to Read-Only

For a backup server which we may want to run as read-only for short maintenance periods the following may be useful.Method 1: Global read_onlyLogin to MySQL and set the read_only setting to true: mysql -u root -pset GLOBAL read_only = true; To unset this run: set GLOBAL read_only = false; Method 2: Read LockAnother method… Read more »

mysqldump

Basic Usage: mysqldump -u root -pSECRET –quote-names –databases¬† mydatabasename > mydatabasename.sql If find it best to include the –databases switch as this will add the ‘DROP DATABASE’ and ‘CREATE DATABASE’ statements when restoring – proceed carefully.Quote Names:Quoting the file names can be an insurance against users who may have created field names with spaces in… Read more »

MySQL 4 to MySQL 5 Character Sets and Collation

Moving from MySQL 4 to MySQL 5 caused a few issues on Joomla websites. The problems stem from MySQL 4 using latin1 swedish as default but some applications like Joomla want utf8 so: 1. Re-export the database (it is Ok to do this from the existing MySQL 5 database): mysqldump -u username -p –default-character-set=latin1 –compatible=mysql40… Read more »

MySQL 4 to MySQL 5 Character Sets and Collation

Moving from MySQL 4 to MySQL 5 caused a few issues on Joomla websites. The problems stem from MySQL 4 using latin1 swedish as default but some applications like Joomla want utf8 so: 1. Re-export the database (it is Ok to do this from the existing MySQL 5 database): mysqldump -u username -p –default-character-set=latin1 –compatible=mysql40… Read more »

MySQL InnoDB Table Lock

I had a customer with a problem using a web based email marketing software application. Importing data and deleting data seemed to stop working. The database uses InnoDB tables so I used phpMyAdmin to try to execute the delete SQL code and received the following error: 1205 lock wait timeout exceeded For future reference: To… Read more »

MySQL Old Passwords

We had a application using version 4 libraries of MySQL to talk to a MySQL 5 database which reported:<br /> Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client The temporary solution: SET PASSWORD FOR ‘some_user’@’some_host’ = OLD_PASSWORD(‘newpwd’);

MySQL Converting ISAM to MyISAM

Some old database tables needed to be converted. This can be done at the mysql command prompt with: ALTER TABLE tbl_name TYPE = MYISAM; Or using the script ‘mysql_convert_table_format’ which is located in the MySQL bin folder. Usage: ./mysql_convert_table_format test –user=root –password=secret be careful as InnoDB tables will also be converted to MyISAM.