# Create User CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']; # Create Database $ mysqladmin -u <username> -p create <nameOfDatabase> # Drop/Delete Database $ mysqladmin -u <username> -p drop <nameOfDatabase> # Check Process List $ mysqladmin -u root -p proc # Check Status at 5 seconds interval $ mysqladmin -u root -p -i 5 status # Dump Database $ mysqldump --opt -u <username> -h <hostname> <nameOfDatabase> -p > /path/to/file $ mysqldump --opt -u <username> -h <hostname> --all-databases -p > /path/to/file # Import Database $ mysql -h <host> -u <username> <nameOfDatabase> -p < /path/to/file GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON <dbname>.* TO <dbuser@localhost> [IDENTIFIED BY '<password>']; REVOKE ALL ON <dbname> FROM <dbuser@localhost>; CREATEDATABASE <dbname>; DROP DATABASE <dbname>; DROP TABLE <tablename1[, table2, table3...]>; # To activate new permissions FLUSH PRIVILEGES; USE <nameOfDatabase>; SHOW DATABASES; # show tables begining with the prefix SHOW TABLES LIKE 'prefix%'; SELECT * FROM <nameOfTable>; DESCRIBE <nameOfTable>; INSERT INTO <table> <username, password, name1, name2, ...> VALUES ('user', password('pass'), 'value1', 'value2' ...); CREATE TABLE <newtable> AS SELECT DISTINCT <field> FROM <oldtable>; INSERT INTO <database.table> SELECT * FROM <database.table> WHERE <field> = <value>; ALTER TABLE <tableOldName> RENAME <tableNewName>; UPDATE <tableName> SET <field1> = <newValue> [WHERE <field2> = <currentValue>];
Bookmark/Search this post with
- sandip's blog
- Login or register to post comments
Comments
Check and make sure that character_set% variable are all set to utf8 first:
mysql> SHOW VARIABLES LIKE 'character_set%';Set all to utf8 except filesystem and dir.
mysql> set character_set_database='umysql> set character_set_server='utf
Then import the data and verify:
mysql> LOAD DATA INFILE '/path/to/file.csv' INTO TABLE utf8_table FIELDS TERMINATED BY ';' ESCAPED BY '\\' IGNORE 1 LINES;mysql> select * from utf8_table limit 10;
Must have SUPER privileges for this to work.
mysql> SET SQL_LOG_BIN = 0;and all queries on current session will not be sent to the binary log.
To turn binary logging back on:
mysql> SET SQL_LOG_BIN = 1;mysql> SET @@GLOBAL.SQL_SLAVE_SKIP_COUNTEIf sysv init script fail to shutdown mysql server, then try with the mysqladmin command:
mysqladmin shutdownThis gets a list of tables and pipes to xargs to form a mysqldump command with the table names appended.
mysql --batch --skip-column-names {database_name} -e 'show tables like "wp_%"' | \xargs mysqldump --opt {database_name} > /tmp/dump.sql
To safely purge binary log files:
1. On each slave server, use SHOW SLAVE STATUS to check which log file it is reading.
2. Obtain a listing of the binary log files on the master server with SHOW BINARY LOGS.
3. Determine the earliest log file among all the slaves. This is the target file.
4. Purge all log files up to but not including the target file.
PURGE BINARY LOGS TO 'mysql-bin.010';Reference: http://dev.mysql.com/doc/refma
Show schema and tables using InnoDB engine:
mysql> use information_schema;mysql> select table_schema, table_name from tables where engine = 'InnoDB';
SELECT engine,&nbs
&nbs
&nbs
&nbs
&nbs
&nbs
&nbs
&nbs
&nbs
Ref: https://www.percona.com/blog/2
mysql> show engines;Cache query result:
SELECT SQL_CACHE * FROM table;Do not cache query result:
SELECT SQL_NO_CACHE * FROM table;Below is example of mysqldump using where clause of a "time_stamp" column:
mysqldump --opt -where="time_stamp > '2010-08-01'" <database> <table> | gzip >database.table.sql.gzTo get additional information about table.
myisamchk -dvv table.MYIIf using php-5.3.x with mysqlnd support, you would need to update all passwords to use the new longer 41-byte hash.
mysql> set session old_passwords=0;mysql> select password('oldpassword
mysql> set password for 'username'@'loc
Show databases and number of tables:
mysqlshow -vmysqlshow -v <dbname>mysqlshow --status -v <dbname>Show procedures:
mysql> show procedure status;Show functions:
mysql> show function status;Dump procedures and functions for the respective dbs':
$ mysqldump --opt --routines -uroot -p {DB} > DB.sqlCheck for indexes:
mysql> SHOW INDEX FROM <tableName> [FROM <nameOfDatabase>]Adding Indexes:
mysql> ALTER TABLE `<tableName>` ADD INDEX (`<field>`);Dropping Indexes:
mysql> ALTER TABLE `<tableName>` DROP INDEX `<field>`;Additional grants:
mysql> grant lock tables on `<db_name>`.* to '<username>'@&Removing grants:
mysql> revoke lock tables on `<db_name>`.* from '<username>'@&Set global variable:
mysql> SET @@global.<variable>=<Set session variable:
mysql> SET @@session.<variable>=<Listing:
mysql> SELECT @@[global|session].<variablReference:
Dynamic System Variables
mysql> GRANT ALL PRIVILEGES ON *.* TO '<user>'@'mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhomysql> show grants for 'user'@'localhomysql> DROP USER ''@'localhost&#Read it at: Jeremys' Blog
OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file.
Remember to do this periodically and after every upgrade.
OPTIMIZE TABLE for MyISAM tables is equivalent of running:
mysqlcheck is meant to be used when mysqld is running, and myisamchk is supposed to be used when mysqld is down only.
You're right.
If mysqlcheck does not work for you when repairing tables:
mysqlcheck -r -e <database> <table>Then try stop the mysql server and run:
myisamchk -r <table>.MYIStart up mysql and you may still have to run `mysqlcheck -r` once again if an upgrade is requested.
To check all database tables first on a running database:
mysqlcheck -c --all-databasesFor a detailed explaination of checking and repairing Tables refer to the MySQL Manual.