Posts Categorized: mysql

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:

‘@’

‘ = OLD_PASSWORD(‘

‘);

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.

MySQL: marked as crashed and should be repaired

I was receiving the following error message in a MySQL database. SQL Error: Table ‘./dbname/tablename’ is marked as crashed and should be repaired So first I tried: REPAIR TABLE `tablename`; However the table was still showing as crashed. Next I tried: mysqlcheck -r -u username -p databasename Which took some time as the database is… Read more »

MySQL Structure Backup

To backup / dump only the structure of a MySQL database (without the data) use the following: mysqldump -u root -p –no-data –all-databases > /var/struct_only.sql