It is often required to get the size of a database in MySQL to understand how much disk space does it need.
It is especially useful to check the size of a database if you prepare to migrate it somewhere or going to setup database backup and it needs to choose the storage capacity.
In this short note i will show the SQL queries that get the information about the required databases from the information_schema and print their sizes in megabytes. (more…)
While working with MySQL databases, it is often required to find out the total number of rows in some table.
There are many different ways to count the table rows in MySQL, but as the performance always matters, the fastest way is the most preferable.
Below you will find the best and the fastest way to get the number of rows in a table using the simple SQL query. (more…)
Sometimes it is required to find out the size of a table in MySQL database.
For example, if it is needed to perform an optimization and you analyze if some tables have become too large and it is time to tweak them somehow.
In this short note i will show the SQL query that gets the information about the required table from the information_schema and prints the table’s size in megabytes. (more…)
Sometimes it is required to compare two tables to check if they are the same or not.
For this you can calculate and compare the checksums of each table, using the CHECKSUM TABLE command in MySQL.
This command is especially useful if you’re using MySQL replication and want to ensure that master and slave are consistent by verifying that their tables are identical. (more…)
In this article i will show how to list MySQL users, their passwords and granted privileges from the command-line prompt.
MySQL account consists of two components: user and host.
This allows the same user to use different MySQL accounts with different privileges, depending on which host they are connecting from.
In the Host field, besides of the common IP addresses and host names, you can see the % sign, that is a wildcard character that means “any” host. (more…)
Sometimes it is needed to run some MySQL queries from the Linux command-line interface without accessing the interactive MySQL prompt.
For example, when it is required to schedule a backup of MySQL database or to automate execution of some SQL queries with a Bash script.
In this article i will show the most useful, from my point of view, options of the MySQL command-line client and show how to run multiple SQL queries to a database from a Bash script. (more…)
From this small tutorial you will learn how to create a MySQL database from the command-line in Linux.
I will show the general MySQL CREATE DATABASE syntax for creating a database with a default character set.
Additionally i will show how to create a user in MySQL, set him a password, grant all privileges on this newly created database and allow him to access it locally. (more…)
MySQL is the world’s most popular open source relational database management system that runs as a server providing multi-user access to a number of databases.
This guide explains how to install and perform the initial secure configuration of MySQL Server on CentOS/RHEL based systems.
Install MySQL Server and MySQL Client using ‘yum’ command.
# yum install mysql-server mysql
Set the MySQL server daemon to start at boot.
# chkconfig mysqld on
Start MySQL.
# service mysqld start
Run MySQL secure installation script.
# /usr/bin/mysql_secure_installation
Note: Running all parts of this script is recommended for all MySQL servers in production use! Please read each step carefully!
Perform the next steps:
Once these steps are complete you would be able to log into MySQL Server console with the following command:
# mysql -u root -p
Changing a MySQL user’s password is a task handled by the database administrator (root).
Once the MySQL user’s password is changed, you should update the user’s privileges.
Step 1: Log into MySQL server as root
$ mysql -u root -p
Step 2: Use ‘mysql’ database
mysql> use mysql;
Step 3: Change password for user John
mysql> update user set password=PASSWORD('NEW-PASSWORD-HERE') where User='John';
Step 4: Reload privileges
mysql> flush privileges;
Do not use phpMyAdmin to restore your MySQL database if the MySQL database is large.
phpMyAdmin has limit on total upload size and, there is also maximum execution time which may cause browser to time out.
The solution of how to restore large MySQL database from the sql dump file is to use Linux/Unix command line.
If you need to restore a database that has already been existed on the server, than you have to use ‘mysqlimport‘ command.
The syntax for ‘mysqlimport’:
$ mysqlimport -u [username] -p[password] [dbname] [backupfile.sql]
Note: There is no space between -p and the password.
Example:
$ mysqlimport -u root -pSeCrEt customers_db customers_db_backup.sql
1. Create an appropriately named Database on the target server.
Example:
mysql> CREATE DATABASE customers_db;
2. Load the sql dump file using the ‘mysql‘ command:
$ mysql -u [username] -p[password] [db_to_restore] < [backupfile.sql]
Example:
$ mysql -u root -pSeCrEt customers_db < customers_db_backup.sql
If the sql dump file is compressed, you can do the following:
$ gunzip < [backupfile.sql.gz] | mysql -u [username] -p[password] [dbname]
Example:
$ gunzip < customers_db_backup.sql.gz | mysql -u root -pSeCrEt customers_db