Useful commands for use with PHPMyAdmin or the MySQL / MariaDB console
Click the title below for basic examples of how to use these commands from the MariaDB or MySQL terminal.
User Creation, Manipulation, Deletion
- Show all users in the mysql database:
- Delete a null user:
- Delete all users who are not root:
- Change root user to a less-guessable name (for security):
- Create a new DBA:
- Add a new user with specific privileges on a specific database:
- Add a new DBA with all privileges to a specific database:
- Add a new DBA with all privileges to a specific database + grant option inheritance to other users: [Use with caution]
- Change a user or DBA password:
- Delete a user or DBA from the database:
- Show grants and permissions for a user or DBA:
MariaDB [(none)]> select * from mysql.user;
MariaDB [(none)]> delete from mysql.user where user = ' ';
MariaDB [(none)]> delete from mysql.user where not (host="localhost" and user="root");
MariaDB [(none)]> update mysql.user set user="dbadmin" where user="root";
MariaDB [(none)]> grant all privileges on *.* TO 'username'@'localhost' identified by 'strong_password';
MariaDB [(none)]> grant select, insert, update, delete on database_name.* TO 'username'@'localhost' identified by 'strong_password';
MariaDB [(none)]> grant create, drop, index, alter on database_name.* TO 'username'@'localhost' identified by 'strong_password';
MariaDB [(none)]> grant create temporary tables, lock tables on database_name.* TO 'username'@'localhost' identified by 'strong_password';
Available Options: select, insert, update, delete, create, drop, index, alter, create temporary tables, lock tables
MariaDB [(none)]> grant all privileges on database_name.* TO 'username'@'localhost' identified by 'strong_password';
MariaDB [(none)]> grant all privileges on database_name.* TO 'username'@'localhost' identified by 'strong_password' with grant option;
MariaDB [(none)]> update mysql.user set password=oldpass("strong_password") where user='username';
MariaDB [(none)]> delete from mysql.user where user="username";
MariaDB [(none)]>show grants for ‘username‘@‘localhost‘;
Simple Database Manipulation & Queries
- Show all databases:
- Create a database:
- Use a specific database:
- Delete a database:
- Optimize a single database or all databases:
- Rename a database from command line (not from within MariaDB), there is purposely no space between -p and ‘mypass’
- Show the last 200 queries to your database with the sample table name "queries" and the sample field "query_id":
MariaDB [(none)]> show databases;
MariaDB [(none)]> create database database_name;
MariaDB [(none)]> use database_name;
MariaDB [(none)]> drop database database_name;
All Databases:
$ sudo mysqlcheck -o --all-databases -u root -p
Single Database:
$ sudo mysqlcheck -o db_name -u username -p
mysqldump -u dbauser -pmypass database_name > database_name.sql
mysql -u dbauser -pmypass new_database_name < database_name.sql
MariaDB [(none)]> select * from queries order by query_id desc limit 200;
Simple Table Manipulation
- Show all tables within a previously selected database:
- Show data within a specific table in a previously selected database:
- Rename a table within a specific database:
- Drop a table from a specific database:
MariaDB [(none)]> show tables;
MariaDB [(none)]> select * from my_table;
MariaDB [(none)]> rename table first TO second;
or
MariaDB [(none)]> alter table my_table rename as my_newtable;
MariaDB [(none)]> drop table my_table;
Simple Column and Field Manipulation
- Show columns within a table:
- Update a field within a previously selected database table:
- Select data within a specific table in a previously selected database:
- Insert data into a table and its columns within a specific database:
- Delete data from specific fields within a column:
- Update data in a specific table, column, field:
MariaDB [(none)]> desc my_table;
or
MariaDB [(none)]> show columns from my_table;
MariaDB [(none)]> update my_table set my_column="newinfo" where my_column="oldinfo";
MariaDB [(none)]> select * from my_table where my_column='my_data' order by my_column2;
MariaDB [(none)]> insert into my_table values('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');
MariaDB [(none)]> delete from my_table where mycolumn="my_data";
MariaDB [(none)]> update my_table set column1="my_data" where column2="my_data";
Simple Backup Options
-p and strong_password)- Backup all databases:
- Restore all databases:
- Backup a single database:
- Restore a single database:
- Backup all databases compressed with bzip2
- Backup all databases compressed with gzip
- Backup a single database structure only (tables, columns, rows), no data:
- Backing up only the database structure of specific databases, not the actual data:
- Backup a specific database and specific tables within that database only:
- Mini script to backup all databases and tables using gzip:
mysqldump -u root -pstrong_password --all-databases > all-databases.sql
mysql -u username -pstrong_password < all-databases.sql
mysqldump -u root -pstrong_password database_name > database_name.sql
mysql -u username -pstrong_password database_name < database_name.sql
mysqldump --all-databases | bzip2 -c > all-databases.sql.bz2 (use bunzip2 to uncompress)
mysqldump --all-databases | gzip > all-databases.sql.gz (use gunzip to uncompress)
mysqldump --no-data --databases database_name > database_name_structure_only.sql
mysqldump --no-data --databases db1 db2 db3 > db1_db2_db3_structure_only.sql
mysqldump --add-drop-table -u username -pmypass database_name table_1 table_2 > database_name_t1_t2.sql
1 2 3 | #!/bin/sh date=`date -I` mysqldump --all-databases | gzip > /var/backup/all-databases-$date.sql.gz |
Recover lost root password
systemctl stop mariadb.service/usr/bin/mysqld_safe --skip-grant-tables &mysql -h localhostUPDATE mysql.user set password=password("strong_password") where User='root' and host='localhost';exit;systemctl start mariadb.service
Example Output:
1 2 3 4 5 6 7 8 | MariaDB [(none)]> USE mysql; MariaDB [(none)]> UPDATE user -> SET password=password('strong_password') -> WHERE user='root' AND host='localhost'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [(none)]> exit; Bye |