Common Useful MySQL / MariaDB Commands

Useful commands for use with PHPMyAdmin or the terminal

User Creation, Manipulation, Deletion

  • Show all users in the mysql database:

    • MariaDB [(none)]> select * from mysql.user;

  • Delete a null user:

    • MariaDB [(none)]> delete from mysql.user where user = ' ';

  • Delete all users who are not root:

    • MariaDB [(none)]> delete from mysql.user where not (host="localhost" and user="root");

  • Change root user to a less-guessable name (for security):

    • MariaDB [(none)]> update mysql.user set user="dbadmin" where user="root";

  • Create a new DBA:

    • MariaDB [(none)]> grant all privileges on *.* TO 'username'@'localhost' identified by 'strong_password' with grant option;

  • Add a new user with specific privileges on a specific database:

    • MariaDB [(none)]> grant select, insert, update, delete, create, drop, index, alter, create temporary tables, lock tables on database_name.* TO 'username'@'localhost' identified by 'strong_password';

  • Add a new DBA with all privileges to a specific database:

    • MariaDB [(none)]> grant all privileges on database_name.* TO 'username'@'localhost' identified by 'strong_password' with grant option;

  • Change a user or DBA password:

    • MariaDB [(none)]> UPDATE mysql.user set password=oldpass("strong_password") where User='username';

  • Delete a user or DBA from the database:

    • MariaDB [(none)]> delete from mysql.user where user="username";

Simple Database Manipulation & Queries

  • Show all databases:

    • MariaDB [(none)]> show databases;

  • Create a database:

    • MariaDB [(none)]> create database database_name;

  • Use a specific database:

    • MariaDB [(none)]> use database_name;

  • Delete a database:

    • MariaDB [(none)]> drop database database_name;

  • Optimize a single database or all databases:

    • All Databases:
    $ sudo mysqlcheck -o --all-databases -u root -p

    Single Database:
    $ sudo mysqlcheck -o db_name -u username -p

  • Rename a database from command line (not from within MariaDB), there is purposely no space between -p and ‘mypass’

    • mysqldump -u dbauser -pmypass database_name > database_name.sql

    mysql -u dbauser -pmypass new_database_name < database_name.sql

  • Show the last 200 queries to your database with the sample table name "queries" and the sample field "query_id":

    • MariaDB [(none)]> select * from queries order by query_id desc limit 200;

Simple Table Manipulation

  • Show all tables within a previously selected database:

    • MariaDB [(none)]> show tables;

  • Show data within a specific table in a previously selected database:

    • MariaDB [(none)]> select * from my_table;

  • Rename a table within a specific database:

    • MariaDB [(none)]> rename table first TO second;
    or
    MariaDB [(none)]> alter table my_table rename as my_newtable;

  • Drop a table from a specific database:

    • MariaDB [(none)]> drop table my_table;

Simple Column and Field Manipulation

  • Show columns within a table:

    • MariaDB [(none)]> desc my_table;
    or
    MariaDB [(none)]> show columns from my_table;

  • Update a field within a previously selected database table:

    • MariaDB [(none)]> update my_table set my_column="newinfo" where my_column="oldinfo";

  • Select data within a specific table in a previously selected database:

    • MariaDB [(none)]> select * from my_table where my_column='my_data' order by my_column2;

  • Insert data into a table and its columns within a specific database:

    • MariaDB [(none)]> insert into my_table values('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');

  • Delete data from specific fields within a column:

    • MariaDB [(none)]> delete from my_table where mycolumn="my_data";

  • Update data in a specific table, column, field:

    • MariaDB [(none)]> update my_table set column1="my_data" where column2="my_data";

Simple Backup Options

Important: (no space in between -p and strong_password)

  • Backup all databases:

    • mysqldump -u root -pstrong_password --all-databases > all-databases.sql

  • Restore all databases:

    • mysql -u username -pstrong_password < all-databases.sql

  • Backup a single database:

    • mysqldump -u root -pstrong_password database_name > database_name.sql

  • Restore a single database:

    • mysql -u username -pstrong_password database_name < database_name.sql

  • Backup all databases compressed with bzip2

    • mysqldump --all-databases | bzip2 -c > all-databases.sql.bz2 (use bunzip2 to uncompress)

  • Backup all databases compressed with gzip

    • mysqldump --all-databases | gzip > all-databases.sql.gz (use gunzip to uncompress)

  • Backup a single database structure only (tables, columns, rows), no data:

    • mysqldump --no-data --databases database_name > database_name_structure_only.sql

  • Backing up only the database structure of specific databases, not the actual data:

    • mysqldump --no-data --databases db1 db2 db3 > db1_db2_db3_structure_only.sql

  • Backup a specific database and specific tables within that database only:

    • mysqldump --add-drop-table -u username -pmypass database_name table_1 table_2 > database_name_t1_t2.sql

  • Mini script to backup all databases and tables using gzip:

Last Modified: 10 Oct, 2023 at 03:05:21