https://blog.engineyard.com/2015/introduction-to-mysqls-innodb-memcached-interface

Moodle MySQL Replication using PHPMyAdmin

The phpMyAdmin that is bundled with cPanel doesn’t include user management so the following commands needed to be run to give the replication user the correct permissions:

Setting up the Master , add the slave replication user. The IP below is the slave IP.

mysql -e “GRANT REPLICATION CLIENT ON *.* TO ‘user_replicate’@’216.xx.xxx.xxx’;”
mysql -e “GRANT REPLICATION SLAVE ON *.* TO ‘user_replicate’@’216.xx.xxx.xxx’;
mysql -e “FLUSH PRIVILEGES;”

You should now be able to add the user user_replicate under Replication in phpMyAdmin.

Add to my.cnf

# slow_query_log=1 to your config file.
# slow_query_log_file=/var/log/mysqld.slow.log

nano /var/lib/mysql/server.com.err

141212 17:26:27 [ERROR] Plugin 'InnoDB' init function returned error.
141212 17:26:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed 

There are two solutions to this particular problem:

1. Restore the my.cnf file to its original state, with an innodb_log_file_size equal to the actual size of the existing InnoDB log files.

2. Rename or move both the ./ib_logfile0 and ./ib_logfile1 files, and then start the MySQL server.
The ./ib_logfile0 and ./ib_logfile1 files are located in the InnoDB data directory (usually /var/lib/mysql). Both files must be moved or renamed for the above procedure to work. When starting MySQL, new InnoDB log files of the appropriate size will be created.

The original InnoDB log files only need to be kept as long as they may be needed for data recovery. If the MySQL server successfully starts after the above procedure, and all data is intact, the original InnoDB log files can be discarded.

The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are:

PURGE BINARY LOGS TO ‘binlogname’;
PURGE BINARY LOGS BEFORE ‘datetimestamp’;

These will clear all binary logs before the binlog or timestamp you just specified.

For example, if you login to mysql run

mysql> PURGE BINARY LOGS TO 'mysql-bin.000223';

this will erase all binary logs before ‘mysql-bin.000223’.

If you run

mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;

this will erase all binary logs before midnight 3 days ago.

If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:

mysql> SET GLOBAL expire_logs_days = 3;

then add this to /etc/my.cnf

[mysqld]
expire-logs-days=3

and mysqld will delete them logs for you
SHOW SLAVE STATUS\G

This is critical. When you run SHOW SLAVE STATUS\G, you will see two binary logs from the Master:

Master_Log_File
Relay_Master_Log_File

When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File is, and go back to the Master and run

PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';

To clean up Binary Log on Master Server

shell> mysql -u username -p
mysql> RESET MASTER;

To clean up Binary Log on Slave Server

mysql -u username -p
mysql> RESET SLAVE;

Remove MySQL Binary Log with PURGE BINARY LOGS Statement
PURGE BINARY LOGS statement can remove Binary Log based on date or up to a Binary Log sequence number
Base on the binary logs example shown above, I would like to remove binary up to mysql-bin.000015

shell> mysql -u username -p
mysql>PURGE BINARY LOGS TO 'mysql-bin.000015';

Alternatively, you can remove the binary older than a specific date.

shell> mysql -u username -p
mysql> PURGE BINARY LOGS BEFORE '2009-11-01 00:00:00';

Remove MySQL Binary Log with mysqladmin flush-logs Command
Another method is running mysqladmin flush-logs command, it will remove binary logs more than 3 days old.

shell> mysqladmin -u username -p flush-logs

Keep MySQL Binary Log for X Days

All of the methods above required monitoring on disk usage, to “rotate” and keep the binary logs for x number of day. The option below can be configured on MySQL’s config file, my.cnf

expire_logs_days = 7

Consider turning off MySQL Binary Log if MySQL Replication is not deploy on the database server and recovery is not the main concern.

nano /etc/my.cnf
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

If you drive is 100% full

Mysql error log shows –

ERROR 3 (HY000): Error writing file './mysql-bin.~rec~' (Errcode: 28)

and the commands do not work, try this:

Stop mysql or kill the process
Zero out the first log file
remove the line for the first binary file in mysql-bin.index
restart mysql
Then run the commands to purge

https://wordpress.org/support/topic/too-many-mysql-connections-and-mysql-server-has-gone-away

Check the error logs:

[11-Nov-2014 11:04:24 UTC] WordPress database error MySQL server has gone away for query SELECT ID FROM wp_posts WHERE post_title = 'blog' AND post_type= 'page' made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/cricket_new/page-templates/live_score.php'), include('/themes/cricket_new/includes/right_part.php'), get_page_by_title

Check Mysql

mysql> show status like '%onn%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| Aborted_connects         | 835     |
| Connections              | 3998523 |
| Max_used_connections     | 533     |
| Ssl_client_connects      | 0       |
| Ssl_connect_renegotiates | 0       |
| Ssl_finished_connects    | 0       |
| Threads_connected        | 3       |
+--------------------------+---------+

Export, Copy and Restore a Mysql Database to a remote server

export database

# mysqlexport -uroot databasename > databasename.sql

Copy to other server

#scp databasename.sql root@remote.box:/backup

Restore

# mysql -u user -p databasename < databasename.sql [/bash] How do I copy a MySQL database from one computer/server to another? Other commands: [bash] # mysqldump db-name | mysql -h remote.box.com db-name [/bash] Use ssh if you don't have direct access to remote mysql server (secure method): [bash] # mysqldump db-name | ssh user@remote.box.com mysql db-name [/bash] OR [bash] # mysqldump -u username -p'password' databasename | ssh user@remote.box mysql -u username -p'password databsename [/bash] You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax: [bash] # mysqldump db-name foo | ssh user@remote.box.com mysql bar [/bash] OR [bash] # mysqldump -u user -p'password' db-name foo | ssh user@remote.box.com mysql -u user -p'password' db-name foo [/bash] Bash Script [bash] /bin/bash DBUSER=user DBPASSWORD=pwd DBSNAME=sourceDb DBNAME=destinationDb DBSERVER=db.example.com fCreateTable="" fInsertData="" echo "Copying database ... (may take a while ...)" DBCONN="-h ${DBSERVER} -u ${DBUSER} --password=${DBPASSWORD}" echo "DROP DATABASE IF EXISTS ${DBNAME}" | mysql ${DBCONN} echo "CREATE DATABASE ${DBNAME}" | mysql ${DBCONN} for TABLE in `echo "SHOW TABLES" | mysql $DBCONN $DBSNAME | tail -n +2`; do createTable=`echo "SHOW CREATE TABLE ${TABLE}"|mysql -B -r $DBCONN $DBSNAME|tail -n +2|cut -f 2-` fCreateTable="${fCreateTable} ; ${createTable}" insertData="INSERT INTO ${DBNAME}.${TABLE} SELECT * FROM ${DBSNAME}.${TABLE}" fInsertData="${fInsertData} ; ${insertData}" done; echo "$fCreateTable ; $fInsertData" | mysql $DBCONN $DBNAME [/bash] Copy the /var/lib/mysql folder to the new server. Only if the databases are Mysiam 1. Shut down mysql 2. rename the current /var/liv/mysql to /var/liv/mysql-bak 3. copy the old /var/liv/mysql to /var/lib/mysql 4. Restore file ownership to mysql:mysql for everything in /var/lib/mysql: [bash] # sudo chown -R mysql:mysql /var/lib/mysql [/bash] 5. mysql_upgrade -u root -p Notes: It is the mysql root password from the old system! The file /var/lib/mysql/mysql_upgrade_info needs to be writable. 6. Restart Mysql [bash] # service restart mysqld [/bash] Also - check out this - Percona XtraBackup http://www.percona.com/software/percona-xtrabackup