Open Source Database

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

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. With generic asynchronous MySQL Replication we often use skip_slave_counter to tackle / skip anonymous transactions which creates errors or halt MySQL Replication. But if we try to skip slave counter whilst GTID_MOD is ON we get following error. In this case we should do following steps Stop Replication. .Set GTID_MODE to ON_PERMISSIVE Skip slave counter. Start slave. Set GTID_MODE to ON once it catches the master. GTID_MODE is dynamic variable which can be set on the run. Here we need to look at GTID_MODE following options. OFF: Both new and replicated transactions must be anonymous. OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions. ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions. ON: Both new and replicated tran...