Difference between revisions of "MySQL"
Rootadminacc (talk | contribs) |
Rootadminacc (talk | contribs) m (→MySQLdump) |
||
(22 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
Note: MySQL on Fedora and CentOS can be called mysqld | Note: MySQL on Fedora and CentOS can be called mysqld | ||
== Backup a database == | |||
See http://serverkb.co.uk/wiki/MySQL#MySQLdump | |||
== Before making changes... == | == Before making changes... == | ||
Line 40: | Line 44: | ||
== [http://rackerhacker.com/2007/04/27/plesk-sql-statements Check email passwords] == | == [http://rackerhacker.com/2007/04/27/plesk-sql-statements Check email passwords] == | ||
== Check MySQL users == | |||
Short versions: | |||
<pre>select User from mysql.user; | |||
select User,Host from mysql.user;</pre> | |||
Long version: | |||
<pre>select * from mysql.user\G</pre> | |||
== [http://www.pantz.org/software/mysql/mysqlcommands.html Common Commands] == | == [http://www.pantz.org/software/mysql/mysqlcommands.html Common Commands] == | ||
Line 134: | Line 149: | ||
UTF-8 UTF utf-8 utf | UTF-8 UTF utf-8 utf | ||
== Create table with text == | |||
<pre>create table TableName ( ColumnName TEXT );</pre> | |||
or | |||
<pre>create table TableName ( | |||
ColumnName TEXT );</pre> | |||
== 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: | |||
<pre>SHOW ENGINE INNODB STATUS\G</pre> | |||
<pre>show variables like 'innodb_lock_wait_timeout'; | |||
set innodb_lock_wait_timeout=100</pre> | |||
You can also set it to a higher value in /etc/my.cnf permanently with this line: | |||
<pre>[mysqld] | |||
innodb_lock_wait_timeout=120</pre> | |||
and then restart mysql. If you cannot restart mysql at this time, run this: | |||
<pre>SET GLOBAL innodb_lock_wait_timeout = 120;</pre> | |||
Check the list of locked tables: | |||
<pre>show open tables where in_use>0;</pre> | |||
Check the list of the current processes, one of them might be locking your table(s): | |||
<pre>mysql> show processlist;</pre> | |||
You can then kill one of these processes: | |||
<pre>kill put_process_id_here;</pre> | |||
== Delete user == | == Delete user == | ||
Line 159: | Line 217: | ||
To dump in XML format, use --xml after mysqldump | To dump in XML format, use --xml after mysqldump | ||
== Dump database/s & copy database/s from one server to another == | == Dump a database == | ||
If you have a backup of the MySQL database in a .sql file, delete the database first within mysql: | |||
<pre>drop database NameOfDatabaseToBeDeleted;</pre> | |||
Then dump the .sql file into MySQL: | |||
<pre>mysql -uroot -p < database.sql</pre> | |||
== Dump database/s & copy database/s from one Plesk server to another == | |||
'''Linux''' | '''Linux''' | ||
Line 266: | Line 334: | ||
C:\Program Files (x86)\Parallels\Plesk\MySQL\Data\my is the my.cnf equivalent. | 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: | |||
<pre>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";</pre> | |||
== Flat Files == | == Flat Files == | ||
Line 280: | Line 378: | ||
== Import sqlite to MySQL == | == Import sqlite to MySQL == | ||
[http://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql Stack Overflow Guide 1] | [http://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql Stack Overflow Guide 1]<br> | ||
[http://stackoverflow.com/questions/4140210/shell-file-to-import-sqlite-into-mysql Stack Overflow Guide 2] | [http://stackoverflow.com/questions/4140210/shell-file-to-import-sqlite-into-mysql Stack Overflow Guide 2]<br> | ||
[https://www.google.co.uk/search?hl=en&safe=off&site=&source=hp&q=import+sqlite+to+mysql&oq=import+sqlite+&aq=5&aqi=g10&aql=&gs_nf=1&gs_l=hp.3.5.0l10.647.6084.0.8595.14.14.0.4.4.0.148.866.8j2.10.0.0kqDvPY0M4E Google search] | [https://www.google.co.uk/search?hl=en&safe=off&site=&source=hp&q=import+sqlite+to+mysql&oq=import+sqlite+&aq=5&aqi=g10&aql=&gs_nf=1&gs_l=hp.3.5.0l10.647.6084.0.8595.14.14.0.4.4.0.148.866.8j2.10.0.0kqDvPY0M4E Google search] | ||
Line 321: | Line 419: | ||
Then test the connection works to the database with that username and password: | Then test the connection works to the database with that username and password: | ||
<pre> mysql -h localhost -u YourUser -DNewDatabase -p</pre> | <pre>mysql -h localhost -u YourUser -DNewDatabase -p</pre> | ||
== Monitor queries == | == Monitor queries == | ||
Line 328: | Line 426: | ||
== MySQLdump == | == MySQLdump == | ||
<pre>mysqldump -u username -p database_to_backup > backup_name.sql</pre> | |||
If you get the following error: | |||
<pre>mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)</pre> | |||
Then run the following command: | |||
<pre>mysql_upgrade -u root -p --force</pre> | |||
Then restart mysql: | |||
<pre>service mysql restart | |||
service mysqld restart | |||
systemctl restart mysql | |||
systemctl restart mysqld</pre> | |||
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 === | === FreeBSD server === | ||
Line 487: | Line 604: | ||
== Reset root password for MySQL == | == Reset root password for MySQL == | ||
Quick way: | Quick way if you already know the password, outside of MySQL do: | ||
<pre>mysqladmin -u root -p'OldPassword' password NewPassword</pre> | <pre>mysqladmin -u root -p'OldPassword' password NewPassword</pre> | ||
Inside MySQL: | |||
<pre>SET PASSWORD FOR 'root'@'localhost'=PASSWORD('MyNewPass');</pre> | |||
If you don't know the current password: | |||
=== [http://kwatog.com/blog/reset-mysql-root-password-in-centos CentOS] === | |||
The same steps basically. | |||
=== Debian === | |||
<pre>/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</pre> | |||
Whilst this is at a blank line, run: | |||
<pre>mysql --user=root mysql | |||
update user set Password=PASSWORD('new-password-here') WHERE User='root'; | |||
flush privileges; | |||
exit</pre> | |||
Then stop and start the server: | |||
/etc/init.d/mysql restart | |||
=== Ubuntu === | |||
<pre>/etc/init.d/mysqld stop | <pre>/etc/init.d/mysqld stop | ||
Line 553: | Line 697: | ||
<pre>/etc/init.d/mysql restart</pre> | <pre>/etc/init.d/mysql restart</pre> | ||
== Run a command externally/outside of MySQL == | |||
mysql -p`cat PasswordFile` -D DatabaseName -e "select * from dbname;" | |||
== Set root password for MySQL == | == Set root password for MySQL == | ||
Line 567: | Line 715: | ||
mysql -p<br> | mysql -p<br> | ||
yourpassword | 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 == | == Show over 1000 rows == | ||
use database | use database | ||
select * from | select * from table where type="HeaderValue" LIMIT 0,99999; | ||
== Show tables column headers == | == Show tables column headers == | ||
show columns from table; | 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: | |||
<pre>apt-get purge mysql-server mysql-common mysql-client</pre> | |||
== Unknown table engine 'InnoDB' == | == Unknown table engine 'InnoDB' == |
Latest revision as of 08:57, 17 May 2019
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.