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 […]
mysql
MySQL: Count Rows in Table – The Fastest Way
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 […]
MySQL: Table Size
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 […]
MySQL: Checksum Table
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 […]
MySQL: Show Users, Privileges and Passwords
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 […]
MySQL: Run Query from Bash Script or Linux Command Line
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 […]
MySQL: Create Database – Command Line
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 […]
MySQL Server Installation and Initial Configuration on CentOS/RHEL
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. MySQL Server Installation Install MySQL Server and MySQL Client using ‘yum’ […]
HowTo: Change a User’s Password in MySQL
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 […]
HowTo: Restore MySQL Database From the Command Line
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 […]