HOW TO TO CALCULATE TABLE SIZE IN MB AND CREATE EXCEL SHEET IN MYSQL 5.7 USING CENTOS 7

 HOW TO TO CALCULATE TABLE SIZE IN MB AND CREATE EXCEL SHEET IN MYSQL 5.7 USING CENTOS 7

1.Given command below:-

SELECT 

     table_schema as `Database`, 

     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

WHERE table_schema = 'mamc_disney'

order BY (data_length + index_length) DESC;

2.create excel sheet, we are simple using outfile function

SELECT 

     table_schema as `Database`, 

     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

WHERE table_schema = 'your_database_name'

order BY (data_length + index_length) DESC

INTO OUTFILE '/var/lib/mysql-files/pankaj.xlxs'

FIELDS TERMINATED BY ',';

Notes:-In mysql predifine  this directory and we can change also not fixed.

How to calculate table size MB in mysql 5.7

 How to calculate table size MB in mysql 5.7

now we are calculate table size in mysql 5.7 using centos 7

SELECT 

     table_schema as `Database`, 

     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

WHERE table_schema = 'your_database_name'

order BY (data_length + index_length) DESC;

How to Change a MySQL Data Directory to a New Location on CentOS 7

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 down MySQL before we actually make changes to the data directory:

[root@localhost ~]# systemctl stop mysqld

[root@localhost ~]# systemctl status mysqld

● mysqld.service - MySQL Server

   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

   Active: inactive (dead) since Tue 2020-09-22 23:49:45 IST; 8s ago

     Docs: man:mysqld(8)

           http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 2812 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

  Process: 2790 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

 Main PID: 2816 (code=exited, status=0/SUCCESS)

Sep 22 23:42:47 localhost.localdomain systemd[1]: Starting MySQL Server...

Sep 22 23:42:50 localhost.localdomain systemd[1]: Started MySQL Server.

Sep 22 23:49:43 localhost.localdomain systemd[1]: Stopping MySQL Server...

Sep 22 23:49:45 localhost.localdomain systemd[1]: Stopped MySQL Server.

step.3. 

To create a new mysql directory.

mkdir -p /home/data/mysql/

chmod 755 /home/data/mysql/

chown mysql:mysql /home/data/mysql/

step.4.

Then copy recursively the contents of /var/lib/mysql to /home/data/mysql/ preserving original permissions and timestamps:

[root@localhost ~]# cp -R -p /var/lib/mysql/* /home/data/mysql

step.5.

Configure a New MySQL Data Directory

[root@localhost ~]# vi /etc/my.cnf

BEFORE MYSQL DIRECTORY

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

socket=/var/lib/mysql/mysql.sock

AFTER EDIT MySQL DIRECTORY

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

datadir=/home/data/mysql

socket=/home/data/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

#socket=/var/lib/mysql/mysql.sock

socket=/home/data/mysql/mysql.sock

step.6.

Restarting MySQL

[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# systemctl status mysqld

● mysqld.service - MySQL Server

   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

   Active: active (running) since Wed 2020-09-23 00:57:20 IST; 19s ago

     Docs: man:mysqld(8)

           http://dev.mysql.com/doc/refman/en/using-systemd.html

  Process: 4368 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

  Process: 4342 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

 Main PID: 4371 (mysqld)

   CGroup: /system.slice/mysqld.service

           └─4371 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Sep 23 00:57:16 localhost.localdomain systemd[1]: Starting MySQL Server...

Sep 23 00:57:20 localhost.localdomain systemd[1]: Started MySQL Server.

step.7. finally login mysql databases.

[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         |

+-------------------+

| /home/data/mysql/ |

+-------------------+

1 row in set (0.00 sec)

step.8.

finallly directroy looking in image

Thank You.

HOW TO SETUP MYSQL MASTER MASTER REPLICATION WITH GTID.

 #######################

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 GTID mode enabled by setting the gtid_mode variable to ON, 

and with the enforce_gtid_consistency variable enabled to ensure that only statements which are safe for GTID-based replication are logged.

 vi /etc/my.cnf

Add the following parameter under [mysqld] section of my.cnf file

server-id = 1

log-bin = mysql-bin

binlog_format = row

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

Now start the mysqld server process

sudo service mysqld start

Step 3 : Create a replication user for Master serverB

create user 'repl_user'@'%' identified by 'XXXXXXXXXX';

Grant replication slave on *.* to 'repl_user'@'%';

Step 4 : Take backup with mysqldump with Master serverA

Now we take a backup with mysqldump from the master:

mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user=root  --password=XXXXXXXX > mysqlbackup_dump.sql

Step 5 : Configure the Master serverB 

 vi /etc/my.cnf

Add the following parameter under [mysqld] section of my.cnf file

server-id = 2

log-bin = mysql-bin

relay-log = relay-log-server

read-only = ON

gtid-mode=ON

enforce-gtid-consistency

log-slave-updates

Now start the mysqld server process

sudo service mysqld start

Step 6: Create a replication user for Master serverA

create user 'repl_user'@'%' identified by 'XXXXXXXXXX';

Grant replication slave on *.* to 'repl_user'@'%';

Step 7 : Load the mysqldump in Master server 2 

mysql>  source mysqlbackup_dump.sql

Step 8 : Execute the Change Master  on Master serverB 

Now we will tell the Master serverB to use Master serverA as a data source

CHANGE MASTER TO

MASTER_HOST = 'xx.xx.xx.xx',

MASTER_PORT = 3306,

MASTER_USER = 'repl_user',

MASTER_PASSWORD = 'XXXXXXXXX',

MASTER_AUTO_POSITION = 1;

Start the slave And then check status.

start slave;

show slave status\G

If every thing fine then move to master serverA to apply change master.

Step 9 : Execute the Change Master  on Master serverA

Now we will tell the Master serverA to use Master serverB as a data source

CHANGE MASTER TO

MASTER_HOST = 'xx.xx.xx.xx',

MASTER_PORT = 3306,

MASTER_USER = 'repl_user',

MASTER_PASSWORD = 'XXXXXXXXX',

MASTER_AUTO_POSITION = 1;

Start the slave And then check status.

start slave;

show slave status\G

Test the Replication

Create a table on both server and check whether the replication is working fine.

Thank you.

HOW TO CONFIGURATION MASTER MASTER REPLICATION IN MYSQL 5.7 DATABASES

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_schema
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 1
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1

SERVER B

server-id=2
log-bin="mysql-bin"
binlog-ignore-db=test
binlog-ignore-db=information_schema
replicate-ignore-db=test
replicate-ignore-db=information_schema
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 2
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1

As you can see the cfg is almost identical, except for the server-id and auto-increment-offsets values.
Please notice that this configuration will replicate/mirror each and every db with the sole exception of test and information_schema. You might also want to keep out other databases such as mysql or other ones: if that’s the case, just add those you want to exclude by adding a binlog-ignore-db and replicate-ignore-db command for each one of them.

Just delete each and every binlog-ignore-db and replicate-ignore-db replacing them with binlog-add-db and replicate-add-db instead, specifying only the database you need/want to mirror.

Once completed, restart the MySQL server:

systemctl restart mysqld

note:Both server A and server B Restart.

STEP 2. CREATE THE REPLICATION USER BOTH SERVER A AND SERVER B.

mysql -u root -p [root_password]

mysql>CREATE USER 'rpl_user'@'%' IDENTIFIED BY '[replicator_password]';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'rpl_user'@'%' IDENTIFIED BY '[replicator_password]';


STEP 3. CONFIGURE REPLICATION FROM SERVER A TO SERVER B

The next thing we need to to is to replicate all the commands received by Server A to Server B:
in other words, we need to configure Server B as a slave of Server A.

Login to Server A and run following command

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      | test, informatio |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

You’re going to use the File and Position column values in a few

Now connect to Server B  and type the following sql commands:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'Server A IP Address',
MASTER_USER = 'replicator',
MASTER_PASSWORD = '[replicator_password]',
MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
START SLAVE;

Replace Server A IP Address with Server A numerical IPv4 address,
[replicator_password] with the password you choose for your replicator user during Step 2,
mysql-bin.000001 and 107 with the File and Position column values retrieved above

STEP 4. CONFIGURE REPLICATION FROM SERVER B TO SERVER A

Now you need to do the same thing from the other side.
That is, replicate all the commands received by Server B to Server A, configuring the latter as a slave of the former.
Connect to Server B and type the following sql command:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | example      | test, informatio |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


Now connect to Server A and type the following sql commands:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST = 'Server B IP Address',
MASTER_USER = 'replicator',
MASTER_PASSWORD = '[replicator_password]',
MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
START SLAVE;

Reboot both servers now


Step 5: TEST THE REPLICATION

Your MySQL Servers are now in Master-Master replication mode. You can test it in a number of ways. such as:

    1.create a new database using your favorite tool (or via MySQL console) on Server A, then                     connect to Server B to see if it’s also there.
    2.create a new user on Server B, then connect to Server A to see if it’s also there.
    3.issue some insert, delete and/or update query commands from Server A,
and check that the same data will be inserted, deleted and/or modified also in Server B.

How to install mysql and configure SSL?

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    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_crl       |                            |
| ssl_crlpath   |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+
9 rows in set (0.02 sec)

how to enable ssl

step.1.

Download and install the required mysql related packages

yum install mysql-server perl-DBD-MySQL perl-DBI

step.2. 

create a directory

[root@node2 ~]# mkdir -p /home/mysql/certs/

step.3.

You need to issue certificates with 2048 bits and a validity of 3650 days. After this period, the certificates must be renewed or recreated. Depending on your requirements, you might lower the time frame for validity.

# Generate CA file

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

# Generate server certifacte

openssl req -newkey rsa:2048 -days 3560 -nodes -keyout server-key.pem > server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Generate client certificate

openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

# Verify certificates

openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

step.4.

[root@node2 certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

server-cert.pem: OK

client-cert.pem: OK

step.5 .Add the following into the "/etc/my.cnf" file, under the "[mysqld]" section.

[root@node2 certs]# vi /etc/my.cnf

[mysqld]

#SSL

#ssl=1

ssl-ca=/home/mysql/certs/ca-cert.pem

ssl-cert=/home/mysql/certs/server-cert.pem

ssl-key=/home/mysql/certs/server-key.pem

step.7.Add in the following client section to the "/etc/my.cnf" file.

[root@node2 certs]# vi /etc/my.cnf

[client]

ssl-ca=/home/mysql/certs/ca-cert.pem

ssl-cert=/home/mysql/certs/client-cert.pem

ssl-key=/home/mysql/certs/client-key.pem

step.8. Restart the mysqld service.

[root@node2 certs]# systemctl restart mysqld

step.9.login mysql and check ssl and have_ssl variable

[pankaj@node2 certs]$ mysql --ssl-cert=ca-cert.pem --ssl-key=client-key.pem --ssl-cert=client-cert.pem -u root -p -v -v -v

Enter password:

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

Your MySQL connection id is 262

Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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.

Reading history-file /home/pankaj/.mysql_history

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

You are enforcing ssl conection via unix socket. Please consider

switching ssl off as it does not make connection via unix socket

any more secure.

mysql>

mysql>

mysql>

[root@node3 ~]# mysql -u db_user -h 192.168.15.227 -p

Enter password:

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

Your MySQL connection id is 49

Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>

mysql> show variables like '%ssl%';

+---------------+-----------------------------------+

| Variable_name | Value                             |

+---------------+-----------------------------------+

| have_openssl  | YES                               |

| have_ssl      | YES                               |

| ssl_ca        | /home/mysql/certs/ca-cert.pem     |

| ssl_capath    |                                   |

| ssl_cert      | /home/mysql/certs/server-cert.pem |

| ssl_cipher    |                                   |

| ssl_crl       |                                   |

| ssl_crlpath   |                                   |

| ssl_key       | /home/mysql/certs/server-key.pem  |

+---------------+-----------------------------------+

9 rows in set (0.00 sec)

mysql> show variables like '%have_ssl%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| have_ssl      | YES   |

+---------------+-------+

1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Ssl_cipher';

+---------------+-----------------------------+

| Variable_name | Value                       |

+---------------+-----------------------------+

| Ssl_cipher    | ECDHE-RSA-AES128-GCM-SHA256 |

+---------------+-----------------------------+

1 row in set (0.02 sec)

step.9.

User Creation

Once SSL is configured, any connection to MySQL can optionally choose to use SSL or X509. The use of SSL can be forced using REQUIRE. Using REQUIRE SSL means the client must have access to the "ca-cert.pem" certificate. Using "REQUIRE X509" means the client also needs access to the client certificate and key. We can test this using the following users.

CREATE DATABASE IF NOT EXISTS pankaj DEFAULT CHARACTER SET utf8;

CREATE USER 'ssl_user'@'%' identified 'Ssluser@123';

GRANT ALL PRIVILEGES ON pankaj.* TO 'ssl_user'@'%' REQUIRE SSL WITH GRANT OPTION;

FLUSH PRIVILEGES;

HOW TO RECOVER PERFORMANCE _SCHEMA DATABASES

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                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK

sys.sys_config                                     OK

Upgrade process completed successfully.

Checking if update is needed.

STEP.2.

Restart mysqld service

[root@node2 ~]# systemctl restart mysqld

STEP.3.Login Mysql

[root@node2 ~]# mysql -u root -p

Enter password:

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

Your MySQL connection id is 3

Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

7 rows in set (0.00 sec)

after that checking variable

mysql> show variables like '%ssl%';

+---------------+----------------------------+

| Variable_name | Value                      |

+---------------+----------------------------+

| have_openssl  | DISABLED                   |

| have_ssl      | DISABLED                   |

| ssl_ca        | /etc/mysql/ca-cert.pem     |

| ssl_capath    |                            |

| ssl_cert      | /etc/mysql/server-cert.pem |

| ssl_cipher    |                            |

| ssl_crl       |                            |

| ssl_crlpath   |                            |

| ssl_key       | /etc/mysql/server-key.pem  |

+---------------+----------------------------+

9 rows in set (0.04 sec)