Database

From Things and Stuff Wiki
Revision as of 01:13, 24 July 2012 by Milk (talk | contribs) (→‎Scripts)
Jump to navigation Jump to search


MySQL

Commands

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

SQLite

MariaDB

Web interfaces

Admin

mysqladmin create db_name

mysqldump

Tools

  • 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."

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"

use the --dry-run

Other