Open Source Database

How to Change a MySQL Data Directory to a New Location on CentOS 7 Step 1 : Moving the MySQL Data Directory To prepare for moving MySQL’s data directory, let’s verify the current location by starting an interactive MySQL session using the administrative credentials. [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.20 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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> select @@datadir; +-------------------+ | @@datadir         | +-------------------+ | /var/lib/mysql/ | +-------------------+ 1 row in set (0.00 sec) step.2. To ensure the integrity of the data, we’ll shut dow...

 ####################### HOW TO SETUP MYSQL MASTER MASTER REPLICATION WITH GTID. A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).  This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup.  There is a 1-to-1 mapping between all transactions and all GTIDs.  A GTID is represented as a pair of coordinates, separated by a colon character (:), as shown here: GTID = source_id:transaction_idMySQL_gtid-master-master Advantages : ● Setting up MySQL replication is simple ● Consistency is guaranteed between master and slaves. ● Simple to determine inconsistency ● Fail-over process become much easier. step.1. Get Both server  serverA and serverB ip. serverA ip=xx.xx.xx.xx serverB ip=xx.xx.xx.xx Step 2 : Configure the Master serverA To enable GTID-based replication, each server must be started with...

HOW TO CONFIGURE MASTER MASTER MYSQL 5.7 DATABASE REPLICATION MySQL Master-Master replication adds speed and redundancy for active websites. With replication, two separate MySQL servers act as a cluster. Database clustering is particularly useful for high availability website configurations. Step 1. Installing MySQL Server The first thing you need to do is to install MySQL on Server A and Server B. If you already have a MySQL instance up and running on one of them, just upgrade it to the latest version you feel confident to use and then install that same identical version to the other one. As soon as you’re finished, edit the main config file – /ProgramData/MySQL/MySQL Server 5.x/my.ini on Windows, /etc/mysql/my.cnf on Linux – and type-in the following settings, replacing the existing ones if present: SERVER A server-id=1 log-bin="mysql-bin" binlog-ignore-db=test binlog-ignore-db=information_schema replicate-ignore-db=test replicate-ignore-db=information_...

A MySQL client can establish an encrypted connection to a MySQL server. In standard configuration, a client connection is unencrypted, which can lead to data being intercepted on the way. The MySQL encryption can be done separately for each client connection, so both encrypted and unencrypted connections can be used simultaneously. It can also be configured as required for individual connections. mysql> show variables like '%ssl%'; +---------------+----------------------------+ | Variable_name | Value                      | +---------------+----------------------------+ | have_openssl  | DISABLED                   | | have_ssl      | DISABLED                   | | ssl_ca        | /etc/mysql/ca-cert.pem     | | ssl_capath    |      ...

ERROR 1146 (42S02): Table 'performance_schema.session_variables' doesn't exist STEP.1 UPDATE MYSQL. [root@node2 ~]# mysql_upgrade -u root -p --force Enter password: Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv                                 OK mysql.db                                           OK mysql.engine_cost                                  OK mysql.event                                        OK mysql.func                                  ...

  mysqladmin is a utility the command-line comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc. In this article we’ve compiled some very useful ‘mysqladmin‘ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks. 1. How to set MySQL Root password? If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command. # mysqladmin -u root password YOURNEWPASSWORD 2. How to Change MySQL Root password? If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it w...

STEP.1.Check connection in mysql database mysql>  show variables like '%max_connection%'; +-----------------+-------+ | Variable_name   | Value | +-----------------+-------+ | max_connections | 151   | +-----------------+-------+ 1 row in set (0.00 sec) Step.2.suppose you need 500 user at a time connect in mysql database then you need to increase the no of connection or max_connection variable. There are two way to increase and decrease connection:- 1.Temporary 2.parmament. 1.you need to login mysql as root user and set max_connection variable. mysql>  set global max_connections = 500; Query OK, 0 rows affected (0.00 sec) Important thing you reboot the system your max_connection as well as previous state. mysql>  show variables like '%max_connection%'; +-----------------+-------+ | Variable_name   | Value | +-----------------+-------+ | max_connections | 151   | +-------------...