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 email passwords
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.
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;
Download SQL Server Management Studio
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
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 8.04
Upgrade on Ubuntu 10.04
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.
