Skip to content

Instantly share code, notes, and snippets.

@jason-idk
Created February 1, 2018 03:26
Show Gist options
  • Save jason-idk/a025d6399fe952d8411e7d1c3b882104 to your computer and use it in GitHub Desktop.
Save jason-idk/a025d6399fe952d8411e7d1c3b882104 to your computer and use it in GitHub Desktop.
Some notes I took on mysql over time...
Random MySQL Notes:
#### Login to MySQL on Plesk by issuing:
If you cannot access DB on Ubuntu, check /etc/mysql/debian.cnf it will be in clear text... Im not joking...
(only on debian based systems… this is definitely a hack and not supposed to be widely used.)
mysql -u admin -p`cat /etc/psa/.psa.shadow`
#### Lock tables before dumping database: (and unlock)
flush tables with read lock;
unlock tables;
#### Example of db dump:
mysqldump -u admin -p`cat /etc/psa/.psa.shadow` databasename > /path/to/20160608-databasename.sql
mysqldump -u root -p [options] —all-databases > alldb.sql
After dumping the DB, you should gzip it and then scp it to the new system.
mysqldump -u root -p --all-databases --lock-tables --events --triggers
#### Dump only data, no schema:
mysqldump --no-create-db --no-create-info --skip-triggers <database_name>
#### Dump all grants in readable format:
unset HISTFILE (on command line to not save password in history.)
mysql -pSuPerTot35s3cUr##! -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | mysql -pSuPerTot35s3cUr##! $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
#### Listing databases:
show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| apsc |
| horde |
| mysql |
| phpmyadmin_SUCKS |
| phpmyadmin_SUCKS |
| phpmyadmin_SUCKS |
| phpmyadmin_SUCKS |
| psa |
| dbnsmasd |
| wordpress_6 |
+-------------------------+
14 rows in set (0.00 sec)
#### Using database:
use db_name;
#### List tables for database:
show tables;
#### Show permissions for user:
mysql -uroot -p -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | mysql -uroot -p $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
show grants for ‘user’@‘localhost';
+---------------------------------------------------------------------+
| Grants for user@localhost |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' |
| GRANT ALL PRIVILEGES ON `database`.* TO ‘user’@‘localhost' |
| GRANT ALL PRIVILEGES ON `database_two`.* TO ‘user’@‘localhost' |
+---------------------------------------------------------------------+
3 rows in set (0.00 sec)
#### Show database sizes:
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
#### Show engine for tables in particular DB:
SELECT ‘ENGINE’ FROM ‘information_schema’.’TABLES’ WHERE ‘TABLE_SCHEMA’=‘ctp’;
##### Order to bring in data involving triggers and stored procedures:
1) schema_only.sql --- This only creates tables and assumes that the database you are importing into exists (but does not necessarily have any data)
2) data_only.sql -- This is the insert of data only and assumes all required tables are already created
3) triggers_and_procedures.sql -- This should be only triggers and procedures with no actual data.
### Script to dump all views for a database specified on command line:
#!/bin/bash -e
mysql --skip-column-names --batch -e "select table_name from information_schema.views where table_schema = database()" $* |
xargs --max-args 1 mysqldump $*
#### Show User info
mysql> select user, host, password from mysql.user;
+----------+-------------+-------------------------------------------+
| user | host | password |
+----------+-------------+-------------------------------------------+
| root | localhost | *26D5462B39EdsgfsdfgdfgfdsfE484E00EC9C61A |
| root | 127.0.0.1 | *26D5462sdfgdfgfdsfgsdfgggggg84E00EC9C61A |
| | localhost | |
| | skyi01vma01 | |
| user1sa | localhost | *70A893D8jhgfjhg698B90FE28D1911207879D2B4 |
| user1sa | % | *5650FFD13A51DCCBFD295D06jjhgjgj69584B1FD |
| user1sa | 127.0.0.1 | *5650FFD13A51Dyougfggayy6B9CCE7C69584B1FD |
+----------+-------------+-------------------------------------------+
7 rows in set (0.01 sec)
##### Unset history for mysql (do this so the clear text password is not stored in the history)
mysql -p'passwd' -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | \mysql -p'passwd' $@ | \sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
##### Create User and grant privileges
GRANT USAGE ON *.* TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'P2SSw0d!@#$%@' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'user'@'ip.ip.ip.ip' IDENTIFIED BY ‘P2SSw0d!@#$%@’ WITH GRANT OPTION;
#### Grant permissions for user Continued: IP Address or % for all
^
GRANT USAGE ON *.* TO ‘user_admin'@'ip.ip.ip.ip' IDENTIFIED BY PASSWORD '*F0104B9CCEHASH825569923325';
GRANT ALL PRIVILEGES ON *.* TO ‘user’@‘100.0.0.0/255.0.0.0' IDENTIFIED BY PASSWORD '*F0104B9CCEHASH825569923325';
#### Show active connections to database;
show status like '%onn%';
#### View data from table, (must use database first):
select * from table_name limit 10;
EXTRA ----|
|
#### Start secondary instance of MySQL:
-- make configuration file/log files.
mkdir /var/lib/mysql2
chown -R mysql.mysql /var/lib/mysql2/
mkdir /var/log/mysql2
chown -R mysql.mysql /var/log/mysql2
cp -R /etc/mysql/ /etc/mysql2
cp /etc/my.cnf /etc/my2.cnf
-- Replace necessary directives in config file.
cd /etc/mysql2/
sed -i 's/3306/3307/g' my.cnf
sed -i 's/mysqld.sock/mysqld2.sock/g' my.cnf
sed -i 's/mysqld.pid/mysqld2.pid/g' my.cnf
sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf
-- Initialize default DBs.
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/
-- Start the new instance and connect to it.
mysqld_safe --defaults-file=/etc/mysql2/my.cnf &
mysql -S /var/run/mysqld/mysqld2.sock
-- To stop the secondary instance, do...
mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment