Search for:
phpMyBackupPro – A Web Based MySQL Backup Tool for Linux

phpMyBackupPro &-8211; A Web Based MySQL Backup Tool for Linux &-8211; this Article or News was published on this date:2019-05-28 19:45:13 kindly share it with friends if you find it helpful

phpMyBackupPro is an open source very easy to use web based MySQL backup application, written in PHP language released under the GNU GPL. It allows you to create schedule backups, restore and manage them, download, email, or upload backups to any FTP server and lot more. It also takes File directories backup and upload them on a FTP Server.

It supports three compression levels of backups (No compression, zip or gzip compression). It also supports two alternative security login methods, HTTP or HTML authentication.

Features

Following are some major key features of “phpMyBackupPro“.

  1. Single or Multiple database backup support with or without data, table structure.
  2. Three level of compression supported are no compression, gzip or zip compression.
  3. Create scheduled backups without cron jobs using small PHP script.
  4. Upload backups directly onto FTP server and posting of backups by email.
  5. Only Apache and PHP needed to run on  platforms like Linux, Mac or Windows.
  6. Shell interface to take backups manually or by using cron script.
  7. Whole File directory backup and move them to any FTP server.
  8. Take databases backup from different accounts on several MySQL servers.
  9. Two security authentication methods supported HTTP or HTML login authentication.
  10. Friendly interface and very easy to install and setup.
  11. Multiple language supported.

Taking MySQL backups and restoring them from command line is always a good practice, but if what when you don’t have physical access to server. In that, situation phpMyBackupPro tool comes in handy.

How to Install phpMyBackupPro in RHEL/CentOS/Fedora and Debian/Ubuntu

For installing phpMyBackupPro application, you must have running Apache web server and PHP installed on the server. Let’s install these required packages on the server.

Install Apache and PHP

Install on Red Hat based systems using yum command.

- yum install httpd 
- yum install php php-mysql
- service httpd start

Install on Debian based systems using apt-get command.

- apt-get install apache2 
- apt-get install php5 libapache2-mod-auth-mysql php5-mysql
- service apache2 start

The newest phpMyBackupPro version can be downloaded from phpMyBackupPro website or you may use following “wget” command to download.

- cd /usr/share
- wget http://kaz.dl.sourceforge.net/project/phpmybackup/phpMyBackupPro/phpmyBackupPro%202.4/phpMyBackupPro-2.4.zip

Unzip the phpMyBackupPro zip file under /usr/share/ directory.

- unzip phpMyBackupPro-2.4.zip

For security reasons, it’s better to place the content of the folder under /usr/share/phpmybackup directory.

- cd phpMyBackupPro-2.4
- mv phpMyBackupPro/ /usr/share/phpmybackup
- mv documentation/ /usr/share/phpmybackup/

Next go to Apache “conf.d” directory and create a file named “phpmybackup.conf” under it. For Red Hat based systems path should be (/etc/httpd/conf.d/) and for Debain (/etc/apache2/conf.d).

- vi phpmybackup.conf

Append the following lines to it. Save and close. The below rules by default enable access to all, if you want to restrict the access to specific IP. Replace “all” with your IP address”. For example, the line should be “allow from 172.16.25.125“.

Alias /phpmybackup /usr/share/phpmybackup
Directory /usr/share/phpmybackup>
   Options None
   Order allow,deny
   allow from all
/Directory>

Restart Apache service.

- /etc/init.d/httpd restart (On Red Hat systems)

- /etc/init.d/apache2 restart (On Debian systems)

On some systems, certain files must have write permissions for the file “global_conf.php” and for the “export” directory.

- cd /usr/share/

- chown -R root:apache phpmybackup (On Red Hat systems)

- chown -R root:www-data phpmybackup (On Debian systems)

- cd /usr/share/phpmybackup/
- chmod 0777 global_conf.php
- chmod 0777 export

Now you are almost ready to start phpMyBackupPro. Navigate to the browser and load the config.php file like this.

http://localhost/phpmybackup/config.php
OR
http://ip-address/phpmybackup/config.php

In the configuration tab insert your MySQL details, like hostname, username, password and database name. If you would like to setup FTP to save backups, enter FTP login details as shown below.

phpMyBackupPro ConfigurationphpMyBackupPro Configuration

Configuration Panel

Next, click on “backup” tab to see list of your MySQL database and select the database name that you wish to take backup.

phpMyBackupPro ConfigurationphpMyBackupPro Backup

Backup Panel

Schedule backup has two popular ways to schedule backups:

  1. By including the schedule script into existing application.
  2. By using a hidden frame in a HTML frameset.

To schedule a backup, you must first create a schedule script. Go to “schedule backup” tab.

phpMyBackupPro ConfigurationphpMyBackupPro Schedule Backup

Schedule Backup

Select how often you want a backup to be generated. Then you have to choose the directory of that PHP script which will include the schedule script later. After that select the name of the database to backup, enter a comment, select compression type and finally click on “Show script” button. On the next page you will see the newly created schedule script.

Instead of copying generated code to new file, you can save the code by giving a filename like “schedule_backup.php” in the text box and click on “Save data” to save. For more information read “SCHEDULED_BACKUPS.txt” file under documentation directory.

phpMyBackupPro ConfigurationphpMyBackupPro Backup Script

Backup Script

The “sql queries” tab build to run simple sql queries to the databases or import databases from the local computer.

phpMyBackupPro ConfigurationphpMyBackupPro SQL Query

SQL Query Shell

The “start” tab display your current Apache, PHP and MySQL version information.

phpMyBackupPro ConfigurationphpMyBackupPro System Information

Version Information

phpMyBackupPro is by far the easiest backup solution for MySQL. If you are handling MySQL server, then pMBP is a must needed application that can help you to save your precious data with minimum effort.

Reference Links

phpMyBackupPro Homepage

MySQL Basic Database Administration Commands – Part I

MySQL Basic Database Administration Commands &-8211; Part I &-8211; this Article or News was published on this date:2019-05-28 19:44:55 kindly share it with friends if you find it helpful

Database is a structured set of data stored electronically. The concept of database was known to our ancestors even when there were no computers, however creating and maintaining such database was very tedious job. In a manual database say of 100 pages, if you have to search for all the employees whose salary were less than 10k, just think how much difficult it would have been, then.

In today’s world you just can’t escape Database. Right now millions of database is working around the world to store and fetch data of every kind be it strategic data, employee record or web technologies.

MySQL Administration GuideMySQL Administration Guide

MySQL Administration Guide – Part I

Database is oftenly termed as back-end process, as because it is neither visible to end user nor End User interacts directly with the database. They works on front-end process viz., PHP, VB, ASP.NET, etc. and ask the front end to deal with database in back-end.

There are several database server and client available like Oracle, MySQL, MySQLi, MariaDB, MongoDB etc. The syntax of all of these are more or less the same. Mastering one means gaining control on most of them and learning the queries of a database is very easy and fun.

Lets start with simple queries on database. We will be using MySQL which comes bundled with most of the Linux distributions by default, you could install it manually from repository, if it is not installed by default in your case.

Well a database query is a simple piece of code that is sent to database to get custom and refined result, as required.

Install MySQL Database

Use “yum” or “apt” package manager to install MySQL Database.

- yum install mysql mysql-client mysql-server  (on Yum based Systems)

- apt-get install mysql mysql-client mysql-server (on Apt based Systems)
Start MySQL

Start MySQL database service as:

- service mysqld start
or
- service mysql start

Well installing a MySQL database will take you to the configuration where you are asked to setup admin password, etc. Once finished installing and starting the server go to your MySQL prompt.

- mysql -u root -p

Replace root with your configured username and enter password when prompted, if the login credential is correct, you will be at your MySQL prompt at the blink of your eyes.

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 195 

Server version: 5.5.31-0+wheezy1 (Debian) 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners. 

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

Now carrying out queries at this prompt is very educative and fun.

Create a database sfnews
mysql> create database sfnews ;
Query OK, 1 row affected (0.02 sec) 

mysql>

Note: It reports that the query was correct, means database is created. You can verify your newly created database as.

mysql> show databases; 
+--------------------+
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| sfnews            | 
| test               | 
+--------------------+ 
9 rows in set (0.00 sec) 
mysql>

Note: Notice your database in the above output.

Select Database

Now you need to select the database to work upon it.

mysql> use sfnews;
Database changed
mysql>
Create Tables in MySQL

Here we will be creating a table say “minttec” with three fields as:

mysql> CREATE TABLE minttec (
    -> id Int(3), 
    -> first_name Varchar (15), 
    -> email Varchar(20) 
    -> ); 
Query OK, 0 rows affected (0.08 sec) 
mysql>

Note: The above query says OK which means table was created without any error. To verify the table run the below query.

mysql> show tables; 
+-------------------+ 
| Tables_in_sfnews | 
+-------------------+ 
| minttec           | 
+-------------------+ 

1 row in set (0.00 sec) 

mysql>

Things are going fine till now. Yup! You can view the columns you created in the table “minttec” as:

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)

mysql>

It was nothing less than a magic. Anyway I will tell you about the types of declaration and their meaning.

  1. Int is Integer
  2. Varchar is char having variable length as defined. The value after Type is the length of field up-to which it can store data.

OK now we need to add a column say ‘last_name‘ after column ‘first_name‘.

mysql> ALTER TABLE minttec ADD last_name varchar (20) AFTER first_name; 
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, verify it in your table.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 

4 rows in set (0.00 sec) 

mysql>
Add Column in MySQL

Now we will add a column to the right say a column ‘country‘ to the right of email.

mysql> ALTER TABLE minttec ADD country varchar (15) AFTER email; 
Query OK, 0 rows affected (0.16 sec) 
Records: 0  Duplicates: 0  Warnings: 0 

mysql>

Verify the above column insertion query.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
| country    | varchar(15) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec) 

mysql>
Insert Values in Field

What about inserting values to the field?

mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , '[email protected]' , 'India' );
Query OK, 1 row affected (0.02 sec) 

mysql>

How about inserting more than 1 value at a time in the above table.

mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , '[email protected]' , 'India' ), ('3' , 'user' , 'singh' , '[email protected]' , 'Aus' ), ('4' , 'sfnews' , '[dot]com' , '[email protected]' , 'India' );
Query OK, 3 rows affected (0.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0

Verify the above insertion.

mysql> select * from minttec; 
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+ 
|    1 | Ravi	    | Saive     | [email protected] | India   | 
|    2 | Narad      | Shrestha  | [email protected]     | India   | 
|    3 | user       | singh     | [email protected]      | Aus     | 
|    4 | sfnews    | [dot]com  | [email protected] | India   | 
+------+------------+-----------+-------------------+---------+ 

4 rows in set (0.00 sec)

mysql>
Delete Values in Field

Let’s say the third entry in the above output is invalid and we need to delete the third entry.

mysql> DELETE FROM minttec WHERE id = 3;

Query OK, 1 row affected (0.02 sec)

Verify the above operation.

mysql> select * from minttec;

+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+
|    1 | Ravi       | Saive     | [email protected] | India   | 
|    2 | Narad      | Shrestha  | [email protected]     | India   | 
|    4 | sfnews    | [dot]com  | [email protected] | India   | 
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)
Update Values in Field

The id (=4) needs to be edited.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'sfnews'; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Verify the above query.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'sfnews'; 
Query OK, 1 row affected (0.02 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Note: The above query, as performed is not a good idea. It will change the id to ‘4‘ where ever the first name is ‘sfnews’. It is always a good idea to use more than one column with where clause to get minimal error, as:

mysql> UPDATE minttec SET id = 6 WHERE first_name = 'sfnews'AND last_name = '[dot]com'; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>
Delete Column in MySQL

Let we need to drop (delete) a column we think, is of no importance say ‘country‘ here.

mysql> ALTER TABLE minttec drop country; 
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

Verify the table.

mysql> select * from minttec; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | [email protected] | 
|    2 | Narad      | Shrestha  | [email protected]     | 
|    6 | sfnews    | [dot]com  | [email protected] | 
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec) 

mysql>
Rename Table in MySQL

Don’t you think our table name “minttec” is not very much relevant. How about changing it to sfnews_table.

mysql> RENAME TABLE minttec TO sfnews_table; 
Query OK, 0 rows affected (0.03 sec)

mysql>
List all Tables

See all the tables under the current database.

mysql> show tables; 

+-------------------+ 
| Tables_in_sfnews | 
+-------------------+ 
| sfnews_table     | 
+-------------------+
1 row in set (0.00 sec) 

mysql>

The table has been renamed. Now take a backup of the above MySQL database, in a single line of command without any sophisticated tool. Run the below code at your terminal and not on mysql prompt.

- mysqldump -u root -p sfnews > sfnews.sql

check the dumped file on your desktop which would have contents something like
-- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (i686) --
-- Server version 5.5.31-0+wheezy1 -- 
Dump completed on 2013-09-02 12:55:37

It is always a good idea to maintain Backup of MySQL databases. Restoring the backed up MySQL Data is again a simple line of code you need to run at your terminal prompt and not at your mysql prompt.

But, wait first we will delete the database to verify if our restore is perfect.

Delete a Database
mysql> drop database sfnews; 
Query OK, 1 row affected (0.02 sec)

Check for database ‘sfnews’ on your database server.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| my_database        | 
| mysql              | 
| performance_schema | 
| phpmyadmin         | 
| sisso              | 
| test               | 
+--------------------+

7 rows in set (0.00 sec) 
mysql>

Great! The database is lost, but we need not to worry, we are having the backup.

Restore a Database

To restore lost database, run the following command.

- mysql -u root -p sfnews  sfnews.sql
Enter password:
ERROR 1049 (42000): Unknown database 'sfnews'

OOPS! An error, hey we have not create the database sfnews. So go to your mysql prompt and create a database ‘sfnews‘.

mysql> create database sfnews; 
Query OK, 1 row affected (0.00 sec) 

mysql>

Now time to run restore command at your shell prompt (strictly).

- mysql -u root -p sfnews  sfnews.sql 
Enter password:

Verify your database.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| sfnews            | 
| test               | 
+--------------------+ 
8 rows in set (0.00 sec)

Verify the contents of database.

mysql> show tables from sfnews;

+-------------------+ 
| Tables_in_sfnews | 
+-------------------+ 
| sfnews_table     | 
+-------------------+ 
1 row in set (0.00 sec)

mysql>

Verify the contents of your restored table.

mysql> select * from sfnews_table; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | [email protected] | 
|    2 | Narad      | Shrestha  | [email protected]     | 
|    6 | sfnews    | [dot]com  | [email protected] | 
+------+------------+-----------+-------------------+

3 rows in set (0.00 sec)

This is not the end definitely, we will cover the concept of primary key, foreign key, multiple tables and running queries using simple PHP script in the next part of the article.

Don’t forget to tell us, how you felt while going through the article. Your comments are highly appreciated. Stay Healthy and Tuned, remain connected to sfnews.

Adminer – A Advanced Web Based Databases Administration Tool for Linux

Adminer &-8211; A Advanced Web Based Databases Administration Tool for Linux &-8211; this Article or News was published on this date:2019-05-28 19:29:46 kindly share it with friends if you find it helpful

We always interact with the databases to perform tasks in various way. We may connect directly and execute the tasks using SQL CLI mode or non-DBA user prefer to use GUI tools called phpMyAdmin or phpPgAdmin.

Many of us are aware of phpMyAdmin or phpPgAdmin database management tools. This post will talk about yet another database management tool called Adminer.

Adminer Database AdministrationAdminer Database Administration

Adminer Database Administration Tool

What is Adminer

Adminer (Formerly phpMinAdmin) is a fully featured database management tool written in PHP. Adminer is an alternative to phpMyAdmin where we can manage content in MySQL, SQLite, Oracle, PostgreSQL databases effectively.

There are number of web-based database management tools available. We find Adminer is pretty much user friendly. We assumes that you have already installed Apache, PHP and database of your choice.

Adminer Features

  1. Basic functions: add/remove/modify databases/tables.
  2. Modify database objects (views, triggers, procedures, user permissions, variables, processes etc.)
  3. Execute SQL commands from a text field or a file.
  4. Import and export databases and tables.
  5. Export database, data, structure, views, routines to SQL or CSV.
  6. Show processes and kill them.
  7. Display users and permissions and change them.
  8. Support multi-language.

Prerequisites

  1. Apache web server
  2. Supports PHP 5 with enabled sessions
  3. Database (MySQL, PostgreSQL, SQLite, MongoDB, etc.)

Why use Adminer?

There is no doubt that phpMyAdmin is one of the most popular open source database administration tool for managing the MySQL databases. However, for some reason I think it’s not highly suitable which is the reason, Adminer comes into the picture.

Now, you thinking why Adminer is better alternative to phpMyadmin?. Frankly, saying the list is quite too large and some points may be irrelevant for you. The most important differences are:

  1. Tidier user-friendly interface
  2. Exceptional support for MySQL features
  3. High performance
  4. Less size (only 366kB)
  5. Highly secured

To know more about detailed features and comparison between them, see comparision page.

Installation of Adminer in Linux

Go the official Adminer site and download the latest source files (i.e version 4.0.2) using below link.

  1. http://www.adminer.org/en/-download

Alternatively, you may also grab the latest source package using the following wget command.

[[email protected] ~]- wget http://downloads.sourceforge.net/adminer/adminer-4.0.2.zip

Unzip adminer’s zip file, which will create adminer directory with files.

[[email protected] ~]- unzip adminer-4.0.2.zip

Copy ‘adminer-4.0.2‘ directory into DocumentRoot of your web server.

[[email protected] ~]- cp -r adminer-4.0.2 /var/www/html/		[For RedHat based Systems]

[[email protected] ~]- cp -r adminer-4.0.2 /var/www/			[For Debian based Systems]

Finally, open and point to your browser at ‘adminer‘ directory.

http://localhost/adminer-4.02/adminer
OR
http://ip-address/adminer-4.02/adminer

Enter your username and password of your database to login into panel.

Adminer Database AdministrationAdminer Login Panel

Adminer Login Panel

Post Login Screen
Adminer Database AdministrationAdminer Database Administration

Adminer Database Panel

User Creation
Adminer Database AdministrationAdminer User Creation

Database User Creation

Create Database
Adminer Database AdministrationAdminer Database Create

Create Database in Adminer

Reference Links

Adminer Homepage

Conclusion

Adminer is a very powerful web-based database management tool with rich features. Please try it out and share the experience with us via comments box below.

MyCLI – A MySQL/MariaDB Client with Auto-completion and Syntax-highlighting

MyCLI &-8211; A MySQL/MariaDB Client with Auto-completion and Syntax-highlighting &-8211; this Article or News was published on this date:2019-05-28 16:52:35 kindly share it with friends if you find it helpful

MyCLI is an easy-to-use command line (CLI) interface for the popular database management systems: MySQL, MariaDB, and Percona with auto-completion and syntax highlighting. It is built using prompt_toolkit and requires Python 2.7, 3.3, 3.4, 3.5, and 3.6. It supports secure connections over SSL to the MySQL server.

MyCLI Features

  • When you first start it, a config file is automatically created at ~/.myclirc.
  • Supports auto-completion while typing SQL keywords as well as tables, views and columns in the database.
  • Also supports smart-completion which is enabled by default and will offer suggestions for context-sensitive completion.

For instance:

SELECT * FROM Tab> - this will just show table names. 
SELECT * FROM users WHERE Tab> - this will simply show column names. 
  • Supports syntax highlighting using Pygments.
  • Support for SSL connections.
  • Offers support for multiline queries.
  • It optionally logs every query and its output to a file (note that this is disabled by default).
  • Allows you to save favorite queries (save a query using fs alias and run it with f alias).
  • Supports timing of SQL statements and table rendering.
  • Prints tabular data in an appealing way.

How to Install MyCLI for MySQL and MariaDB in Linux

On Debian/Ubuntu distributions, you can easily install the mycli package using apt command as follows:

$ sudo apt-get update
$ sudo apt-get install mycli

Likewise, Fedora 22+ has a package available for mycli, you can install it using dnf command as below:

$ sudo dnf install mycli

For other Linux distributions such as RHEL/CentOS, you’ll need Python pip tool to install mycli. Start by installing pip with the commands below:

$ sudo yum install pip	

Once pip is installed, you can install mycli as follows:

$ sudo pip install mycli

How to Use MyCLI for MySQL and MariaDB in Linux

Once mycli installed, you can use it like this:

$ mycli -u root -h localhost 

Auto-completion

Easy completions such as keywords and sql-functions.

MySQL Auto CompletionMySQL Auto Completion

MySQL Auto Completion

Smart-completion

Table name completions after the ‘FROM’ keyword.

MySQL Auto CompletionMySQL Smart Completion

MySQL Smart Completion

Alias support

A column completions will work even when table names are aliased.

MySQL Auto CompletionMySQL Alias Support

MySQL Alias Support

Syntax-highlighting

Syntax highlighting for MySQL.

MySQL Auto CompletionMySQL Syntax Highlighting

MySQL Syntax Highlighting

Formatted SQL Output

MySQL Output is automatically piped through less command.

MySQL Auto CompletionMySQL Formatted Output

MySQL Formatted Output

To login into mysql and select a database at the same time, you may use a similar command as follows.

$ mycli local_database
$ mycli -h localhost -u root app_db
$ mycli mysql://[email protected]:3306/django_poll

For more usage options, type:

$ mycli --help

MyCLI Homepage: http://mycli.net/index

Do check out some useful articles for MySQL administration.

  1. 20 MySQL (Mysqladmin) Commands for Database Administration in Linux
  2. How to Change a Default MySQL/MariaDB Data Directory in Linux
  3. 4 Useful Commandline Tools to Monitor MySQL Performance in Linux
  4. How to Change Root Password of MySQL or MariaDB in Linux
  5. MySQL Backup and Restore Commands for Database Administration

That’s all! In this guide, we showed how to install and use mycli with simple commands in Linux. Do share your thought concerning this article via the feedback form below.

How to Reset WordPress Admin Password via MySQL Command Prompt

How to Reset WordPress Admin Password via MySQL Command Prompt &-8211; this Article or News was published on this date:2019-05-28 16:36:36 kindly share it with friends if you find it helpful

Sometimes, a WordPress user, with one of the following capabilities, such as administrator, editor, author, contributor or subscriber, forgets its login credentials, especially the password.

WordPress password can be easily changed via “Lost password” WordPress login form. However, if the WordPress account has no way of accessing his email address, changing the password using this mechanism can be impossible. In such cases, the job of updating a WordPress account password can only be managed by a system administrator with full privileges to MySQL database daemon.

In this guide we will show you how to reset a WordPress account password via MySQL command line in Linux.

Before logging in to MySQL/MariaDB database service, first create a MD5 Hash version of the new password that will be assigned to the account, by issuing the below command.

Replace the “newpass” string used in this example with your own strong password. Copy the password MD5 hash to a file in order to later paste the hash to MySQL user password field.

- echo -n "newpass" | md5sum
Create MD5 WordPress PasswordCreate MD5 WordPress Password

Create MD5 WordPress Password

After you’ve generated the new password MD5 hash, log in to MySQL database with root privileges and issue the below command in order to identify and select the WordPress database. In this case the WordPress database is named “wordpress”.

- mysql -u root -p
MariaDB [(none)]> show databases;
MariaDB [(none)]> use wordpress;
Create MD5 WordPress PasswordConnect and Select WordPress Database

Connect and Select WordPress Database

Next, execute the below command to identify the table responsible for storing WordPress user accounts. Usually the table that stores all user information is wp_users.

Query wp_users table to retrieve all users ID, login name and password and identify the username ID field of the account that needs the password changed.
The username ID value will be used to further update the password.

MariaDB [(none)]> show tables;
MariaDB [(none)]> SELECT ID, user_login, user_pass FROM wp_users;
Create MD5 WordPress PasswordList All WordPress Users in MySQL

List All WordPress Users in MySQL

After you’ve correctly identified the ID of the user that needs the password changed, issue the below command to update his password. Replace the user ID and password MD5 Hash accordingly.

In this case the user ID is 1 and the new password hash is: e6053eb8d35e02ae40beeeacef203c1a.

MariaDB [(none)]> UPDATE wp_users SET user_pass= "e6053eb8d35e02ae40beeeacef203c1a" WHERE ID = 1;
Create MD5 WordPress PasswordReset WordPress Admin Password in MySQL

Reset WordPress Admin Password in MySQL

In case you don’t have an already MD5 hashed password, you can execute MySQL UPDATE command with the password written in plain text, as shown in the below example.

In this case we’ll use MySQL MD5() function to calculate the MD5 hash of the password string.

MariaDB [(none)]> UPDATE wp_users SET user_pass = MD5('the_new_password') WHERE ID=1;

After the password has been updated, query wp_users table with the ID of the user that you’ve changed the password in order to retrieve this user database information.

MariaDB [(none)]> SELECT ID, user_login, user_pass FROM wp_users WHERE ID = 1;

That’s all! Now, inform the user that his password has been updated and it should be able to log in to WordPress with the new password.