Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
-- Set Root Password
mysqladmin -u root password <YOURNEWPASSWORD>
-- Set / Change MySQL Users Passwords from the Linux Shell
mysqladmin -u <username >-h <host> -p password <newpassword>
-- How To Connect to MySQL
mysql -ur <username> -p
mysql -h <hostname> -u <username> -p
-- Create Databse
create databse my_database;
-- Backup Database using mysqldump
mysqldump -u <username> -p <db-name> > /tmp/database-backup.sql
-- with compression
mysqldump -u <root> -p <db-name> | gzip -v > /tmp/database-backup.sql.gz
-- Dump a remote DB from server and send to local machine with gzip
mysqldump -u <username> -p <db-name> | gzip -c | ssh <user@local-machine> `cat > /tmp/database-backup.sql.gz`
-- Dump all DBs on a server
mysqldump -u <username> -p <password> --opt > /tmp/databases.sql
-- Dump and skip broken tables
mysqldump -u <username> -p <db-name >--ignore-table=your-database.broken-table > your-database.sql
-- skip multiple tables
mysqldump -u <username> -p <db-name> --ignore-table=your-database.broken-table --ignore-table=your-database.broken-table2 > your-database.sql
-- Dump a specific table from DB
mysqldump -c -u <username> -p <password> <db-name> <table-name> > /tmp/db-name.table-name.sql
-- Import DB
mysqldump -u <username> -p -h <host> <db-name> < database-backup.sql
-- from .sql.gz
zcat database-backup.sql.gz | mysql -u <usernmae> -p <db-name>
-- from sql command line
> source ./db-backup.sql
-- Select DB
use <db-name>;
-- Show Tables
show tables;
-- Create User
grant usage on *.* to <user@localhost> identified by 'username';
-- grant permission
grant all privileges on my_db.* to <user@localhost>;
-- grant permission to all the things
grant all privileges on *.* to <user@localhost>;
-- Show DB size
cd /var/lib/mysql && ls -lh
-- from sql command line
> SELECT table_schema "<db-name>", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
-- List DBs
show databases;
-- Delete DB
drop database <db-name>
-- Drop Table
drop table <table-name>
-- Reset MySQL root password
-- Stop mysql
/etc/init.d/mysqld stop
-- Start mysql in safe mode
mysqld_safe --skip-grant-tables &
-- login as root
mysql -u root
-- set roo password
use mysql;
UPDATE user SET password=PASSWORD("<new-root-pwd") WHERE user='<root>';
flush privileges;
-- restart mysql service
/etc/init.d/mysql restart
-- Create Table
CREATE TABLE table-name (
firstname VARCHAR(20),
datestamp DATE,
timestamp time,
pgpemail VARCHAR(255)
-- INNODB Table
CREATE TABLE your_table_name_innodb (
id INT,
data VARCHAR(100)
) TYPE=innodb;
-- Convert MyIsam to INNODB
-- Repair Broken Table in MySql
repair table <broken_table_name>;
-- Show DB fields/format
describe <table-name>
-- Select
SELECT * FROM <table-name>
-- Show columns in table
SHOW COLUMNS FROM <table-name>
-- Add a new column
ALTER TABLE <table-name> ADD COLUMN <new-column> varchar (20)
-- Delete column
ALTER TABLE <table-name> DROP column <column-name>
-- Delete row
DELETE FROM <table-name> WHERE <field-name> = '<batman>'
-- Show row count
SELECT COUNT(*) FROM <table-name>
-- Join tables
SELECT <column_names> FROM <table-1>, <table-2> WHERE (table-1.column = table-2.column);
-- Sum column
SELECT SUM(*) FROM <table-name>
-- Show unique records
SELECT DISTINCT <column-name> FROM <table-name>
-- Search using regex
SELECT * FROM <table-name> WHERE <value> RLIKE "^z"
Copy link

robyviggy commented Feb 19, 2016

Good Work!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment