DBA Tasks | mysql.r2schools.com

How to Rename a Database in MySQL

In this article, we will see how to Rename a Database in MySQL. In previous versions of MySQL, RENAME DATABASE is used to rename database. From all newer versions, RENAME DATABASE has been removed to avoid security risks.
We can rename database in MySQL using dump and restore process.

Steps to Rename a Database in MySQL:

mysqldump syntax:

mysqldump –u [UserName] –p[Password] –R [DB_Name] > [DB_Name].sql

Replace [UserName] and [Password] with the actual credentials for the database, and replace [DB_Name] with the exact name of the database you’re changing. There should be no space between -p and the password. The -R flag indicates that the dump file should retain all stored procedures and functions.

1) Create dump using mysqldump tool. Here r2schools is the database name.

mysqldump -u root -p -R r2schools > r2schools.sql

Continue reading How to Rename a Database in MySQL

How to Lock and Unlock MySQL User Account

Lock and unlock is administrator day to day activity. ALTER USER privileges required to lock and unlock MySQL Server User. In the following example, we provided the check the status of MySQL User, lock and unlock the User account.

1. Create MySQL User.

CREATE USER harry IDENTIFIED BY 'Harry@321';

2. Check the status of the MySQL Server whether it is locked or not by running the query.
Continue reading How to Lock and Unlock MySQL User Account

How to Change MySQL User Password

We can change MySQL User Password by using three methods.

1. Alter User Method
2. SET PASSWORD method.
3. Update User method.(This method deprecated from 8.0.11 onwards)

Change MySQL User Password Examples:

1. Create a MySQL User:

CREATE USER amith IDENTIFIED BY 'Amith@321';

2 .Change Password by using Alter User Method:
Continue reading How to Change MySQL User Password

Create User in MySQL

We can create user in MySQL Server by using CREATE USER statement. For each account, CREATE USER creates a new row in the mysql.user system table. To create user in MySQL Server, user must have CREATE USER privileges.

Syntax to Create User in MySQL:

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

Examples Create User in MySQL:
Continue reading Create User in MySQL

How to Start and Stop MySQL Server on CentOS

In this tutorial, we are going to see how to Start and Stop MySQL Server on CentOS operating system. Privileges required to start or stop or restart MySQL Server, user must be superuser.

We can start, stop, and restart the MySQL Server from Linux shell and We can stop and restart MySQL Server Shell.

To check the MySQL Server status execute either of two commands from Linux shell:

service mysqld status

systemctl status mysqld

Start and Stop MySQL Server on Linux — Start and Stop MySQL Server on CentOS