Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Saturday, 31 March 2018

MySQL Server Startup Script file: MySQL pid-file / Socket file- mysqld.sock

What is MySQL pid-file ?

MySQL server create this file automatically on server startup . In this file the server should write its process ID.

If this option is not given, mysql.server uses a default value of host_name.pid.

pid-file=/var/lib/mysql/your-db.pid
[mysqld_safe]

# Basic safe settings

log-error = /var/log/getmysql.log

pid-file = /var/lib/mysql/getmysql.pid 

mysql> show variables like '%pid%';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| pid_file      | /var/lib/mysql/getmysql.pid   |
+---------------+-------------------------------+
Some time on mysqld startup ,You are facing some issues like..
  • mysql pid file missing !!
  • mysql pid not found !!
  • mysql pid permission denied !!
  • mysql pid error !!
  • mysql pid file does not exist !! etc...

PID file should automatically created when you start mysql service. but if you face any of mentioned error you need to manually delete existing pid and then check the required permissions (0777) on PID file directory.
e.g.
chmod 0777 /var/lib/mysql/ib_logfile0
chmod 0777 /var/lib/mysql/ib_logfile1
chown -R mysql:root /var/lib/mysql
After that MySQL service will start properly .

What is MySQL Socket file / mysql.sock ?

MySQL Socket file does not contains any data or it is not a normal file like .log/.ini/.config/.txt etc.
MySQL automatically create this file on server startup. This file is just like a PIPE which MySQL use it as pipe to exchanges network packets between a client and server. It is only used locally.
[mysqld]
# Basic settings
user = mysql 
datadir = /var/lib/mysql 
socket = /var/lib/mysql/mysql.sock 
In server startup,if you face any error like
  • mysql.sock file missing !!
  • mysql.sock not found !!
  • mysql.sock permission denied !!
  • mysql.sock error !!
  • mysql.sock file does not exist !! etc...

You need to manually delete existing .sock file and then check the required permissions (0777) on .sock file directory.


Friday, 30 March 2018

MySQL Group Replication

In this article I will explain you just basic introduction of Group Replication. Why you use it and how to configure Group replication on your environment.

To achieve and providing highly available replica sets , MySQL introduce Group Replication Beta release0.8 for MySQL 5.7.14 available for download on labs.mysql.com, Group Replication will be supported on all MySQL 5.7 supported platforms.

Group Replication is a plugin for the standard MySQL 5.7 Server. This plugin provides virtually synchronous replication, with built-in conflict detection/handling and consistency guarantees, all of which supports multi-master write anywhere usage. It allows you to move from a stand-alone instance of MySQL, which is a single point of failure, to a natively distributed highly available MySQL service (the Group Replication set) that's made up of N MySQL instances (the group members). Then individual machines and/or MySQL instances can fail or be taken offline for maintenance while the distributed MySQL service continues to operate and handle application traffic.

Why you use Group Replication>>

If you need to avoid MySQL downtime for a serious impact of your business or its operations, then you should use Group Replication.
This means that most user-facing production systems should leverage Group Replication as it provides a native means for making a MySQL database highly available.

How to configure Group Replication >>

In this article , Considering 3 Server to setup Group Replication. We have tested Group replication within 3 Machine with single instance. You can configure and test in different-2 scenario as per you convenience.

First of all you need to install MySQL 5.7 packages including the Group Replication plugin (the group_replication.so file located in the @@global.plugin_dir directory) on the machines that you want to participate in Group Replication.

Below is showing our configuration command and testing result:
Group Replication with 3 Server 
 
 Note in my Testing :
* 1: Group replication supported mysql server 5.7.__ and later     
* 2: When member is online in group then Query browser for that memebr not functioning. 
* 3: loose_group_replication_allow_local_disjoint_gtids_join=True (in my.ini )
***************************************************************************************
 
Group replication settings for server1 with 3 server in group
In my.ini
[mysqld] port=3306 server_id=1 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog-format=ROW # *******Group replication section******** transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.200.178:3306" loose-group_replication_group_seeds= "192.168.200.178:3306,192.168.200.72:3306,192.168.200.198:3306" loose-group_replication_bootstrap_group= off **************************************************************************************** Command line mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0,00 sec) mysql> CREATE USER replalok@'%'; Query OK, 0 rows affected (0,00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO replalok@'%' IDENTIFIED BY 'replalok'; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0,00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0,00 sec) mysql> SET sql_log_bin=0; # disable GTID generation and binary logging for our session mysql> CHANGE MASTER TO MASTER_USER='replalok', MASTER_PASSWORD='replalok' FOR CHANNEL 'group_replication_recovery'; mysql> SET sql_log_bin=1; # re-enable GTID generation and binary logging for our session cmd>> INSTALL PLUGIN group_replication SONAME 'group_replication.dll'; mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+ mysql> SET GLOBAL group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+---------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+---------------+ | group_replication_applier | ce9be252-2b71-11e6-b8f4-00212844f856 | myhost | 24801 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+---------------+ 1 row in set (0,00 sec)
Group replication settings for Server2 with 3 server in group 
In my.ini
[mysqld] port=3306 server_id=2 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog-format=ROW # *******Group replication section******** transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.200.72:3306" loose-group_replication_group_seeds= "192.168.200.178:3306,192.168.200.72:3306,192.168.200.198:3306" loose-group_replication_bootstrap_group= off ************************************************************************************************** Command line mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0,00 sec) mysql> CREATE USER replalok@'%'; Query OK, 0 rows affected (0,00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO replalok@'%' IDENTIFIED BY 'replalok'; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0,00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0,00 sec) mysql> SET sql_log_bin=0; # disable GTID generation and binary logging for our session mysql> CHANGE MASTER TO MASTER_USER='replalok', MASTER_PASSWORD='replalok' FOR CHANNEL 'group_replication_recovery'; mysql> SET sql_log_bin=1; # re-enable GTID generation and binary logging for our session cmd>> INSTALL PLUGIN group_replication SONAME 'group_replication.dll'; mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+ mysql> ##Here no need to issue this command because group is already bootstraped on member1 ( SET GLOBAL group_replication_bootstrap_group=ON;) mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | da66ff48-03d1-11e7-a2c9-0a0027000002 | DESKTOP-EI5QAB5 | 3306 | ONLINE | | group_replication_applier | f3bc827f-f8cf-11e6-b744-b870f47692c7 | DESKTOP-M6CGSJ4 | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ 2 rows in set (0.00 sec)
Group replication settings for Server3 with 3 server in group
In my.ini
[mysqld] port=3306 server_id=3 gtid_mode=ON enforce_gtid_consistency=ON master_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON log_bin=binlog binlog-format=ROW # *******Group replication section******** transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.200.198:3306" loose-group_replication_group_seeds= "192.168.200.178:3306,192.168.200.72:3306,192.168.200.198:3306" loose-group_replication_bootstrap_group= off **************************************************************************************************** Command line mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0,00 sec) mysql> CREATE USER replalok@'%'; Query OK, 0 rows affected (0,00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO replalok@'%' IDENTIFIED BY 'replalok'; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0,00 sec) mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0,00 sec) mysql> SET sql_log_bin=0; # disable GTID generation and binary logging for our session mysql> CHANGE MASTER TO MASTER_USER='replalok', MASTER_PASSWORD='replalok' FOR CHANNEL 'group_replication_recovery'; mysql> SET sql_log_bin=1; # re-enable GTID generation and binary logging for our session cmd>> INSTALL PLUGIN group_replication SONAME 'group_replication.dll'; mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+-------------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+-------------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | PROPRIETARY | (...) | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | PROPRIETARY | +----------------------------+----------+--------------------+----------------------+-------------+ mysql> ##Here no need to issue this command because group is already bootstraped on member1 ( SET GLOBAL group_replication_bootstrap_group=ON;) mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ | group_replication_applier | da66ff48-03d1-11e7-a2c9-0a0027000002 | DESKTOP-EI5QAB5 | 3306 | ONLINE | | group_replication_applier | f3bc827f-f8cf-11e6-b744-b870f47692c7 | DESKTOP-M6CGSJ4 | 3306 | ONLINE | | group_replication_applier | c9ae915l-l9dg-03h8-s469-x637h18420m1 | DESKTOP-A69GSJ4 | 3306 | ONLINE | +---------------------------+--------------------------------------+-----------------+-------------+--------------+ 2 rows in set (0.00 sec)
Now You can see all 3 Servers are Online.These servers are now added in group replication.You can perform transactions on any servers as Master and same will be propogated to other one.

For any further query Click Here!

Your Feedback is highly appreciated.

Wednesday, 21 March 2018

How to reset MySQL root password in Linux ?

Reset MySQL Root Password: Linux Systems


In this article, I am discussing about how to reset MySQL Root password for 'root'@'localhost' once you forget or lost.

You must have root access on the server to reset the MySQL root password.

To change the password for a root account with a different host name part, modify the instructions to use that host name.

1.    Log in to your account using SSH.
You must run the commands in the following steps as the root user. Therefore, you can either log in directly as the root user (which is not recommended for security reasons), or use the su or sudo commands to run the commands as the root user.

2.    Stop MySQL service.
1: For CentOS
           service mysqld stop
2: For Debian and Ubuntu
           service mysql stop

3.      Restart MySQL server with the –skip-grant-tables option.

mysqld_safe –skip-grant-tables &
·       Must add & (ampersand) at end of above command. This runs the command in the background and allows you to type the commands in the following steps
·       Running MySQL with the —skip-grant-tables option enabled is highly insecure, we request you please perform this task in minimum time period. The steps below show you how to stop the mysqld_safe server instance safely and start the MySQL server securely after you have reset the root password.


4.   Log into MySQL using the following command:
mysql

5.  At the mysql> prompt, reset the password. To do this, type the following command, replacing NEW-PASSWORD with the new root password:
Update mysql.user set password=‘GetMySQL’ where user=’root’;

6.    At the mysql> prompt, type the following commands:
FLUSH PRIVILEGES;
Exit;

7.    Stop the MySQL server using the following command. You will be prompted to enter the new MySQL root password before the MySQL server shuts down:
mysqladmin –u root –p shutdown

8.   Start the MySQL server normally. To do this, type the appropriate command for your Linux distribution:
1: For CentOS and Fedora type:
           service mysqld start
2: For Debian and Ubuntu:
           service mysql start

How to reset MySQL root password in windows ?

Reset MySQL Root Password: Windows Systems
In this article, I am discussing about how to reset MySQL Root password for 'root'@'localhost'  once you forget or lost.
To change the password for a root account with a different host name part, modify the instructions to use that host name.

1. Log on to WINDOWS as Administrator.
2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list and stop it.
If your server is not running as a service, you may need to use the Task Manager to force it to stop.
3. Create a text file containing the password-assignment statement on a single line. Replace the password with the password that you want to use.
in MySQL 5.7.6 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'GetMySQL';
Or
ALTER USER 'root'@'192.168.200.201' IDENTIFIED BY 'GetMySQL';


in MySQL 5.7.5 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('GetMySQL');
Or
SET PASSWORD FOR 'root'@'192.168.200.201' = PASSWORD('GetMySQL');


4. Save the file. This example assumes that you name the file C:\mysql-init.txt.

5. Open a console window to get to the command prompt: From the Start menu, select Run, then enter cmd as the command to be run.

6. Start the MySQL server with the special --init-file option (notice that the backslash in the option value is doubled):


C:\Program Files\MySQL\MySQL Server 5.6>cd bin

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqld.exe --init-file= C:\\mysql-init.txt


If you installed MySQL to a different location, you can change the command line accordingly.

C:\Program Files\MySQL\GetMySQL\MySQL Server 5.6>cd bin

C:\Program Files\MySQL\GetMySQL\MySQL Server 5.6\bin>mysqld.exe --init-file=C:\\mysql-init.txt


The server executes the contents of the file named by the --init-file option at startup, changing the'root'@'localhost' account password.

To have server output to appear in the console window rather than in a log file, add the --console option to the mysqld command.

If you installed MySQL using the MySQL Installation Wizard, you may need to specify a --defaults-file option. For example:

C:\> mysqld
         --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 5.7\\my.ini"
         --init-file=C:\\mysql-init.txt

The appropriate --defaults-file setting can be found using the Services Manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list, right-click it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.

7. After the server has started successfully, delete C:\mysql-init.txt.

You should now be able to connect to the MySQL server as root using the new password.

If the ALTER USER statement fails to reset the password, try repeating the procedure using the following statements to modify the user table directly:

UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Friday, 9 March 2018

What is the difference between Database and DBMS?

Database is a collection of related data stored in several tables and linked through different-different keys. We can create and have several databases in a Database-product instance .

DataBase Management System (DBMS) is a piece of software that manages databases and lets you create, edit and delete databases, their tables and their data.
DBMS is a concept and a set of rules that all or major Database Systems follows. Some Database systems follows most of these rules, and some follows only only certain rules.
Most Database products/tools uses SQL language, which is just a ANSI standard, and is used as a base with most Database systems, like Microsoft SQL Server, Oracle, MySQL, IBM DB2, PostgreSQL and SQLite have used this as a base and extended/enhanced in their database systems.

Thursday, 8 March 2018

Master -Master Replication in Active-Active and Active-Passive mode

In this article I'm explaining how exactly Master-master Replication working in different Mode.

Master-Master replication is working is two mode . 
1: Active-Active Mode 
2: Active-Passive Mode

In both scenario the architecture/Configuration is same but in case of active-active mode we need to more attention on monitoring .

As I already shown the flow diagram of Master -Master replication in Fig1.

Fig1:Architecture of Master-Master Replication
There are two important threads working in replication.

1: Slave_IO Thread 
2: Slave_SQL Thread

Slave_IO thread reads the binlog file written by other Master and write the same in it's Relay log and then Slave_SQL thread read it's own relay log file and execute the transactions .

Active-Passive Mode:-

In this mode one master is Live and another one is standby only . Transactions are performed only on one master.
eg. We are considering Master1 is active(Live) and Master2 is Passive(Standby - not active).

At a time only master1 writing a binlog (MySql-binlog 1) on master1 accordingly this binlog is read by Slave_IO thread of Master2 and written in Relay log(RelayLog 2) of master2 . After that Slave_SQL thread of master2 read the Relay Log2 and execute the transaction on master2.

And so on the data of Master1 has been replicated on Master2.

Active-Active Mode:- 

In this mode both master server are live, it means simultaneous transaction can perform in both master.

In this mode two important parameter is come in knowledge.
auto_increment_increment= 2
auto_increment_offset  = 1 
[ For more details about these parameters work and how it configure, please Click Here ]

This parameter maintains the consistency of primary Key Value and simultaneous transaction must be replicated with same transaction id.

At a time master1 writing a binlog (MySql-binlog 1) on master1 and  master2 writing a binlog (MySql-binlog 2) on master2. 
Slave_IO thread of master2 read "Mysql-binlog 1" and write a RelayLog2 on master2 .After that  Slave_SQL thread of master2 read the Relay Log2 and execute the transaction on master2. 
As same as  Slave_IO thread of master1 read "Mysql-binlog 2" and write a RelayLog1 on master1. After that  Slave_SQL thread of master1 read the Relay Log1 and execute the transaction on master1. 



Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...