Open Source Database

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

 ####################### 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 se

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    |                            | | ssl_cert      | /etc/mysql/server-cert.pem | | ssl_cipher    |                            | | ssl_crl       |                            | | ssl_crlpath   |                            | | ssl_key       | /et

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.