MySQL

Note: MySQL on Fedora and CentOS can be called mysqld

Backup a database

See http://serverkb.co.uk/wiki/MySQL#MySQLdump

Before making changes...

Use the commands BEGIN, COMMIT and ROLLBACK

If your format is InnoDB, the majority of MySQL data is stored in the ibdata file.

Change character encoding

mysql -uadmin -p`cat /etc/psa/.psa.shadow`;
use mysql 
#or the database you want so for example; use moodle
show variables like "character_set_database";

+------------------------+--------+
| Variable_name          | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

alter database DatabaseName character set UTF8;
Query OK, 1 row affected (0.00 sec)
show variables like "character_set_database";

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8 |
+------------------------+-------+
1 row in set (0.00 sec)

Exit MySQL and restart to be safe.

Check database size

http://www.novell.com/communities/node/8706/check-mysql-database-size-using-sql-query
http://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size

Check MySQL users

Short versions:

select User from mysql.user;
select User,Host from mysql.user;

Long version:

select * from mysql.user\G

Common Commands

Log into MySQL, use database and then check MySQL process list which display the database being accessed, the command and the time in minutes it has been running:

show processlist;
show full processlist;

/etc/init.d/mysql status

Check all database/tables for corruption using one of these commands outside MySQL:

mysqlcheck -A

For DH server use:

mysqlcheck -A -uroot -p

If you get the below error:

mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect . Use this command:

mysqlcheck -uadmin -p`cat /etc/psa/.psa.shadow` -A

Skip grant tables:

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &

Note: Table names in MySQL are case sensitive.

locate my.cnf
vim /etc/mysql/my.cnf

For importing large database files, I would recommend using a software like HeidiSQL to import your Databases, as PhpMyAdmin is often problematic when attempting to upload databases of a larger size.

http://www.heidisql.com/

The below command will show you how a database table was created.

show create table tables_name\G

Show all where the start of the entry begins with a

select * from table where name like 'a%';

This is used to return only different values:

SELECT DISTINCT column/row from table;

The ORDER BY keyword is used to sort the result-set.

SELECT column_name
FROM table_name
ORDER BY column_name ASC|DESC

e.g. select * from domains ORDER BY "name" DESC;

To return the number of records in a table:

select count(*) from table;

Connect to MySQL database

mysql -h localhost -u username -d database -p
mysql -h 127.0.0.1 -u username -d database -p

Or

mysql -h localhost -u username -Ddatabase -p

Connect to MySQL remotely

To allow external access to MySQL comment out the following line in my.cnf:

bind-address           = 127.0.0.1
#bind-address           = 127.0.0.1

Then restart MySQL.

Enable remote connection
Connect remotely

To connect from a Linux box to another you can try:

mysql -h IPaddress -uusername -p -D database

Convert all MySQL tables and fields to UTF8

mysql --database=dbname -B -N -e "SHOW TABLES" | awk '{print "ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;"}' | mysql --database=dbname &

This loops through all tables and changes their collations to UTF8. You should backup beforehand though in case some data is lost in the process. Credit to User Root

UTF-8 UTF utf-8 utf

Create table with text

create table TableName ( ColumnName TEXT );

or

create table TableName (
ColumnName TEXT );

Deleting large amount of data

You may get:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You can see more details of the event by issuing:

SHOW ENGINE INNODB STATUS\G

show variables like 'innodb_lock_wait_timeout';
set innodb_lock_wait_timeout=100

You can also set it to a higher value in /etc/my.cnf permanently with this line:

[mysqld]
innodb_lock_wait_timeout=120

and then restart mysql. If you cannot restart mysql at this time, run this:

SET GLOBAL innodb_lock_wait_timeout = 120;

Check the list of locked tables:

show open tables where in_use>0;

Check the list of the current processes, one of them might be locking your table(s):

mysql> show processlist;

You can then kill one of these processes:

kill put_process_id_here;

Delete user

drop user@localhost;

Drop database, table or row/column

drop database DatabaseName;
drop table TableName;

#Delete a row(s) from a table
DELETE from TableName where FieldName] = 'value';

#Delete a column
alter table TableName drop column ColumnName;

Dump a single table

mysqldump -pPASSWORD DatabaseName TableName > table.sql

To dump in XML format, use --xml after mysqldump

Dump a database

If you have a backup of the MySQL database in a .sql file, delete the database first within mysql:

drop database NameOfDatabaseToBeDeleted;

Then dump the .sql file into MySQL:

mysql -uroot -p < database.sql

Dump database/s & copy database/s from one Plesk server to another

Linux

On the old server:

mysqldump -uadmin -p`cat etc/psa/.psa.shadow` <database> > <database>.sql
scp <database>.sql root@VPS_IP:
yes
*Paste the password in*

On the new server; check the mysql databases to ensure it isn't already there, go into Plesk and create it with the same name and then after the command below create a database user:

mysqldump -uadmin -p`cat etc/psa/.psa.shadow` <database> < /root/<database>.sql #or just /

Windows

mysqldump.exe -uroot -pPASSWORD DATABASE > outputdirectory\DATABASE.sql

Backup script

Made by User:Trilium

Enable Slow Query Logging

Only valid for Linux:

wget http://serverkb.co.uk/tools/slow.sh
chmod +x slow.sh
./snow.sh

Entering MySQL and selecting a database/table

On a Plesk server:

mysql -u admin -p`cat /etc/psa/.psa.shadow`;
show databases;
use database (e.g. psa)
show tables;
select * from domains; or \G

or

cat /etc/psa/.psa.shadow
mysql -uadmin -p'$AES...==';

Windows

cd %plesk_dir%\mysql\bin
mysql -uadmin -p

Below goes into the Plesk database MySQL area:

mysql -uadmin -p -P8306

You may need to reset the admin password via Plesk. Get the PW by doing this in Run:

"%plesk_bin%\plesksrvclient" -get

Or:

cd C:\Program Files\MySQL\MySQL Server 4.1\bin

cd C:\Program Files\MySQL\MySQL Server 5.1\bin

C:\Program Files (x86)\Parallels\Plesk\MySQL\Data\my is the my.cnf equivalent.

ERROR 1175

If you get the below error:

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

You will need to do desc TableName; to see which Field uses a Key. For example:

select * from zone_records;
+----------------------------+--------+--------+--------------+-------+-----------------------------+
| zone                       | type   | record | prefix       | ttl   | data                        |
+----------------------------+--------+--------+--------------+-------+-----------------------------+

update zone_records set zone="domain.co.uk" where record="TXT" and prefix="mail";
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

mysql> desc zone_records;
+--------+-------------------------------------------------+------+-----+----------+-------+
| Field  | Type                                            | Null | Key | Default  | Extra |
+--------+-------------------------------------------------+------+-----+----------+-------+
| zone   | varchar(255)                                    | NO   | MUL |          |       |
| type   | enum('username','domain','component','default') | NO   |     | username |       |
| record | enum('A','MX','CNAME','PTR','TXT')              | YES  |     | NULL     |       |
| prefix | varchar(255)                                    | NO   |     |          |       |
| ttl    | int(10) unsigned                                | YES  |     | NULL     |       |
| data   | varchar(255)                                    | NO   |     |          |       |
+--------+-------------------------------------------------+------+-----+----------+-------+

update zone_records set zone="dev.domain.co.uk" where zone="domain.co.uk" and record="TXT" and prefix="mail";

Flat Files

These are the .mdf and .ldf files.

Got a packet bigger than 'max_allowed_packet' bytes

In my.cnf add:

max_allowed_packet = 10M
#or 100M

Import sqlite to MySQL

Stack Overflow Guide 1
Stack Overflow Guide 2
Google search

mysqli

Plesk database location

on Windows is C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\data normally.

Optimizing MySQL database

Utilise functions such as Query Caching to serve results of previous repeated queries to reduce load.

Some of the main cause of databases degradation usually relates to overly complex queries and large log tables, for information on how to improve this performance on Magento please see this and this.

Manually create database and user (Unix)

Create the database:

mysql -p
create database NewDatabase;
show databases;

If you want to dump a database.sql file onto the new database you have just created, do this:

mysqldump -p NewDatabase < database.sql

Create the new user that will be assigned to the database:

create user 'YourUsername'@'localhost' identified by 'plaintextpassword';

Then to assign a user to the database, do this:

use NewDatabase
grant usage on NewDatabase.* to YourUsername@localhost identified by 'plaintextpassword';
grant all privileges on `NewDatabase`.* to 'YourUsername'@'localhost';
quit

Then test the connection works to the database with that username and password:

mysql -h localhost -u YourUser -DNewDatabase -p

Monitor queries

watch -n 1 mysqladmin --user=<user> --password=<password> processlist

MySQLdump

mysqldump -u username -p database_to_backup > backup_name.sql

If you get the following error:

mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

Then run the following command:

mysql_upgrade -u root -p --force

Then restart mysql:

service mysql restart
service mysqld restart
systemctl restart mysql
systemctl restart mysqld

If this doesn't work you may need this fix: http://www.lampdocs.com/couldnt-execute-show-variables-like-gtid_mode-table-performance_schema-session_variables-doesnt-exist-1146-how-to-fix/

FreeBSD server

You may need to chroot into the appropriate environment. The mysqldump location will either be /mysql/bin/mysqldump or /usr/fs//mysql/bin/mysqldump

mysqldump -p<password> <database> > <database>.sql

Multiple exports to .sql files

#!/bin/bash
dbs='firstdb seconddb thirddb'

echo -n 'Enter database password: '
read pw

for db in $dbs
do
    mysqldump -u user -h localhost -p$pw $db > $db_1_$(date +%d%m%y).sql
done

Plesk server

Unix:

mysqldump -uadmin -p`cat etc/psa/.psa.shadow` <database> > <database>.sql

Windows:

mysqldump.exe -uroot -pPASSWORD DATABASE > outputdirectory\DATABASE.sql

MySQL errors/issues

Database already exists importing via PHP My Admin

Go to Tools & Settings > Applications & Databases - Database Servers > Webadmin > Import the Database here

mysql -uadmin -p`cat /etc/psa/.psa.shadow`
show databases;
quit
mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` DatabaseName > /filepath/DatabaseName.sql

Make sure there are no databases in Plesk under domain in Hosting Services - Domains > Control Panel > Websites and Domains > Databases. Remove any if they exist. Then do:

mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` DatabaseName < /filepath/DatabaseName.sql

Go back into Hosting Services - Domains > Control Panel > Websites and Domains > Databases > Database > Add a Database user > Access Webadmin/PHP My Admin

Can't connect to local MySQL server through socket

Warning: mysql_connect() [function.mysql-connect]: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /home/DomainWithoutSuffix/public_html/includes/includes.php on line 10

Warning: mysql_select_db() [function.mysql-select-db]: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /home/DomainWithoutSuffix/public_html/includes/includes.php on line 11

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Restart MySQL (via chroot environment if needed).

Check the error log and if need be increase the innodb buffer pool size from it's default 2M. Also ensure the file /var/run/mysqld/mysqld.sock has mysql:mysql and socket permissions (use mkfifo).

http://forums.mysql.com/read.php?22,423592,423592#msg-423592

Memory allocation error in Plesk

[MySQL][ODBC 3.51 Driver][mysqld-5.0.95-0ubuntu1]Memory allocation error File aps_php.php
Line 7476
Type Exception

How to fix on Ubuntu 10.04 + http://kb.parallels.com/113620

SQLSTATE[HY000]: General error: 1030 Got error 122 from storage engine

Disk space is full normally. Run df -h to find out.

Big BLOB or TEXT rows

InnoDB: ERROR: the age of the last checkpoint is 9433569,
InnoDB: largest such row.
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: which exceeds the log group capacity 9433498.

The error message can mean that you're trying to insert too much data into InnoDB too quickly, and the InnoDB log is filling up before the data can be flushed into the main data files.

To solve it, you need to stop MySQL cleanly (very important), delete the existing InnoDB log files (probably lb_logfile* in your MySQL data directory, unless you've moved them), then adjust theinnodb_log_file_size to suit your needs, and then start MySQL again.

Also see the

<pre>

box in this link.

PHP Connector guides

http://www.phpfreaks.com/forums/index.php?topic=232018.0
http://www.dreamincode.net/forums/topic/80945-php-mysql-connector
http://tycoontalk.freelancer.com/php-forum/19135-php-mysql-connection-problems.html

I would advise reading the following guide in regards to the Windows sections: http://www.ora600.be/node/3511

This refers to the error "Call to undefined function: mysql_connect(). Please install the MySQL Connector for PHP" and can happen due to the PHP installation not having the MySQL compiled within it.

PHP My Admin

1077 Error

When you upload a file into phpMyAdmin and it shows a 1077 error, you are likely uploading the wrong file type. It needs to be as follows:

"File may be compressed (gzip, zip) or uncompressed. A compressed file's name must end in .[format].[compression]. Example: .sql.zip"

If you right click the .sql file and zip it and then try it should work as long as it is below 2GB.

Export issue

The error normally shown is "can’t find the file at https://xx.xx.xx.xx:8443/domains/databases/phpMyAdmin/export.php"

vim /usr/local/psa/admin/conf/php.ini

Find the line containing memory_limit. Increase this to 512M or 1024M.

/etc/init.d/sw-cp-server restart
/etc/init.d/psa stopall
/etc/init.d/psa startall

Repair database/s

To repair one database:

mysqlcheck -uUser --repair DatabaseName -p

or to repair all databases:

mysqlcheck -uUser --repair --all-databases -p

Reset root password for MySQL

Quick way if you already know the password, outside of MySQL do:

mysqladmin -u root -p'OldPassword' password NewPassword

Inside MySQL:

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('MyNewPass');

If you don't know the current password:

CentOS

The same steps basically.

Debian

/etc/init.d/mysql stop
/usr/bin/mysqld_safe --skip-grant-tables &
[1] 32305
130401 22:43:57 mysqld_safe Logging to syslog.
130401 22:43:57 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Whilst this is at a blank line, run:

mysql --user=root mysql
update user set Password=PASSWORD('new-password-here') WHERE User='root';
flush privileges;
exit

Then stop and start the server:

/etc/init.d/mysql restart

Ubuntu

/etc/init.d/mysqld stop
mysqld_safe –skip-grant-tables &

or

mysqld_safe -–skip-grant-tables & (two hyphens)

Then:

mysql -u root

or

mysql -u admin

Then:

use mysql;
update user set password=PASSWORD(“yourpassword”) where User=’root’;

or

update user set password=PASSWORD(“yourpassword”) where User=’admin’

Then

flush privileges;
quit

You should now be able to log in as root/admin using the “yourpassword” that you entered.

Regain lost root MySQL access permissions connecting from localhost

Alternatively do this (on a Plesk server only):

Get the password via the command below, and keep for later*. If it is Plesk 11 you need to copy from the $ up to but not including the word root:

cat /etc/psa/.psa.shadow

vim /etc/mysql/my.cnf

Enter this into the file:

skip-grant-tables

Exit the file. Then restart MySQL:

/etc/init.d/mysql restart
mysql
use mysql
update user set password=PASSWORD ("*") where user="admin";
flush privileges
exit
vim /etc/mysql/my.cnf

Remove skip-grant-tables from /etc/mysql/my.cnf

/etc/init.d/mysql restart

Run a command externally/outside of MySQL

mysql -p`cat PasswordFile` -D DatabaseName -e "select * from dbname;"

Set root password for MySQL

You obviously must know it already:

mysql -p
update user set password=PASSWORD("yourpassword") where User="root";
flush privileges;
quit

Then attempt to access MySQL:

mysql -p
yourpassword

Show users permissions

show grants;

or

SHOW GRANTS FOR 'root'@'localhost';

All users

SELECT User FROM mysql.user;

SELECT * FROM mysql.user;

SELECT CONCAT('SHOW GRANTS FOR ,user,@,host,;') FROM mysql.user;

http://dbadiaries.com/no-mysql-show-users-how-to-list-mysql-user-accounts-and-their-privileges

Show over 1000 rows

use database select * from table where type="HeaderValue" LIMIT 0,99999;

Show tables column headers

show columns from table;

Uninstall MySQL completely (Unix)

If for example you have installed mysql-server, then uninstalled, then installed it again but it is not prompting you for the root password like before, do:

apt-get purge mysql-server mysql-common mysql-client

Unknown table engine 'InnoDB'

If you get the following error:

"MySQL query failed: Unknown table engine 'InnoDB'"

Look inside the /etc/mysql/my.cnf file and ensure you don't have skip-innodb in there. If so, comment out and restart MySQL.

Upgrade on Ubuntu 10.04

Alternate guides: 1, 2, 3

MySQL 5.5. error

If you get the below error when stopping MySQL after upgrading MySQL from 5.1 to 5.5.

/etc/init.d/mysql stop
* MySQL server PID file could not be found!

Do:

ps aux | grep mysql
#this will then show the below output
mysql    11683  0.0  3.5 410276 71276 ?        Sl   06:48   0:00 mysqld --skip-grant-tables --user=mysql

#kill the process id
kill 11683

/etc/init.d/mysql start
/etc/init.d/mysql stop
/etc/init.d/mysql start

ps aux | grep mysql

root 5291 0.1 0.0 4088 668 pts/1 S 09:51 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/serverhostname.pid
mysql 5615 1.1 4.2 621124 84776 pts/1 Sl 09:51 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysql/error.log
--pid-file=/usr/local/mysql/data/serverhostname.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
root 5670 0.0 0.0 6156 696 pts/1 S+ 09:51 0:00 grep mysql

Version 5.5 on CentOS 6 with Plesk

This is really only possible on CentOS 5, as of 08/03/2013

wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
rpm -Uvh remi-release-6*.rpm
vim /etc/yum.repos.d/remi.repo

Set the top [remi] to enabled=1

yum list mysql-server

A script to get the latest MySQL 5.5 for RHEL6

Alternatively, you can use the Atomic repo, however you must disable it afterwards, otherwise it may remove Plesk when it auto-updates.