To create a user in MySQL or MariaDB and granting it permissions to access a database is a basic task when deploying a new website on a LAMP stack server. To read and write data on a database is a very common…
Category: MySQL
How to enable MySQL slow query log and analyze it on Linux
MySQL slow query log is helpful when you needed to debug slow down or failure of your MySQL server. Slow query log can house queries which took more than n number of seconds. Arvind KumarHello Friends, I’m Arvind Kumar, founder…
Export, Import MySQL db between different servers through Linux command line
You should have Database name, Database Username, Database Password and command line access to your server Export database to local file system, for example /var/www/html Arvind KumarHello Friends, I’m Arvind Kumar, founder of Inimist Technologies, Inimist Academy, The Fundabook and…
MYSQL – Useful commands you must know – Part-1
To know the current MYSQL version: SELECT version(); To format and formulate a float value: FORMAT(SUM(amount) * 0.6, 2) The above line sums up entire amount value, cut it down to 60% of total value and returns value precised up…
MySQL ‘show tables’: How to list tables in a MySQL database?
This is very commonly asked question: How do I show or list all the tables in a MySQL (or MariaDB) database using mysql command line tool? In brief, to show the tables in a MySQL database, run the command: Arvind KumarHello Friends, I’m Arvind…
How to search replace string text in a table field in mysql
I have a date field in my mysql database table. I want to replace year 2017 in this field to 2019 Following command would do this. UPDATE races SET `date` =REPLACE( `date` , ‘2018’, ‘2019’ ) WHERE INSTR( `date` , ‘2018’ ) >0 Just saving it for reference here. Arvind KumarHello Friends, I’m Arvind Kumar, founder…
Updating two columns with a subquery in MySQL
Sometimes you wanted to update columns of one MySQL table by quering another MySQL table and getting a column value from the later table. For example you have a ‘students’ table and you wanted to create a new field ‘totalMarks’…
To print the table structure of MySQL table in PHP
You can print the table structure of MySQL table in PHP using simple php script. All you need to do is to make a ‘describe table’ call and process it using php script, similar to one you use to print…
Changing database character set
Why to consider changing database character set? Why do i need to do this? As to find a more straight answer please look at the image below, taken from one of my websites built in WordPress: Arvind KumarHello Friends, I’m…
How to delete duplicate MySQL records with duplicate field value (as ID)
For some reason i had duplicate entries in my WP database. The ID field in wp_users table was tempered with and was changed to Non Auto-Increment to insert records on the will. I deleted all ID=null records manually and then…