Open Source Database

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   | +-------------...

step :1.you are not changing any parameter file both master and slave server my.cnf file. MASTER SERVER:- step.2.you can take full backup in master server. mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events --user='username'  --p'passwd' > mysqldump.sql; step.3.you can transfer backup .sql file master server to slave server. scp  mysqldump.sql ram@'x.x.x.x':. note:- ram is a user name your slave server and (x.x.x.x) is your ip for slave machine. SLAVE SERVER:- step.4.First login mysql in slave server. mysql -u root -p password:-'enter user passwd' step.5.first stop slave server and reset all command. mysql> stop slave; mysql> reset slave all; step.5.after that you are reset master command in slave server. mysql>reset master; step.6.you can also check it both variable gtid_executed and gtid_purged not show any gtid. mysql> show global variables like...

mysql>stop slave; mysql> reset master; mysql > show global variables like '%GTID_EXECUTED%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_executed |       | +---------------+-------+ mysql > show global variables like '%GTID_PURGED%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_purged |       | +---------------+-------+ mysql > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14"; mysql> show global variables like 'gtid_executed'; +---------------+-------------------------------------------------+ | Variable_name | Value                                           | +---------------+-------------------------------------------------+ | gtid_executed | 16a931e0-b829-11e9-b423-0cc47ab45dc4:1-46251417 | +------...