Database
MySQL
- Wikipedia:MySQL
- InnoDB is the default storage engine for MySQL
Commands
- http://www.pantz.org/software/mysql/mysqlcommands.html MySQL Administrator Best Practices]
Account management
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; CREATE DATABASE [databasename];
GRANT all on userdatabase.* TO 'username'; [1]
SHOW GRANTS SHOW GRANTS FOR 'user'@'localhost';
Create command list for showing user grants [2]
SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost';
FLUSH privileges;
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypass');
mysqladmin -u user_name -h host_name password "newpwd"
DB management
SHOW databases; USE [db name]; SHOW tables; SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
13.8.1. DESCRIBE provides information about the columns in a table.
Security
Troubleshooting
- 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
SQLite
MariaDB
Clients
- http://squirrel-sql.sourceforge.net/
- http://www.razorsql.com/
- http://www.heidisql.com/
- http://navicat.com/en/products/navicat_mysql/mysql_detail_linux.html
Web interfaces
- Adminer - single php file
wget http://www.adminer.org/latest-mysql-en.php -O adminer.php wget http://www.adminer.org/latest-en.php -O adminer.php
Admin
mysqladmin create db_name
mysqldump
Tools
- dbdeploy is a Database Change Management tool.
- https://github.com/tanin47/php_db_migrate
- https://bitbucket.org/stepancheg/mysql-diff/wiki/Home
- dBug - "PHP version of ColdFusion’s cfdump. Outputs colored and structured tabular variable information. Variable types supported are: Arrays, Classes/Objects, Database and XML Resources."
- anywhereindb - Sometime we need to find out a small piece of string in big Database. Like where is the configuration is saved, or where is Jon's Date of birth is saved. This code is search all the tables and all the rows and columns in a MYSQL Database. The code is written in PHP. For faster result, we are only searching in the varchar field.
Scripts
- Search Replace DB - This script was made to aid the process of migrating PHP and MySQL based websites. It has additional features for WordPress but works for most other similar CMSes.
./searchreplacedb2cli.php --host localhost --user root --database test --pass "pass"
--charset utf\-8 --search "findMe" --replace "replaceMe"
--dry-run