MySQL replication - slave is lagging behind master

The Seconds_Behind_Master is just like viewing the past via time travel.

Just think like this:

The Sun is 93,000,000 miles away from the Earth
The speed of light is 186,000 miles/sec
Simple division shows that it takes roughly 500 sec (8 min 20 sec) for the Sun's light to reach Earth
When you look at the Sun, you actually don't see the Sun. You see where it was 8 min 20 sec ago.
In same manner, it seems that the Master is processing a lot of queries at the same time.

You look back at the Slave,
run SHOW SLAVE STATUS\G and it says 200 for Seconds_Behind_Master.
How is that number calculated?
Slave's Clock Time (UNIX_TIMESTAMP(NOW()) - TIMESTAMP of the Query
when it was completed and recorded in the Master's Binary Log.

Enable InnoDB Engine MySQL Database server

ERROR 1286 (42000) at line 1: Unknown table engine 'innodb'

Stop MySQL service:
# /etc/init.d/mysql stop

Open the file /etc/my.cnf (
Find the skip-innodb parameter and either remove it or comment the line by adding # at the beginning of the line:

#skip-innodb
Insert the following parameters into the [mysqld] and [mysqld_safe] sections in /etc/my.cnf:
innodb_buffer_pool_size=16M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

Save the file and start MySQL:
# /etc/init.d/mysql start

In case still innoDB not showing
# /etc/init.d/mysql stop
move ibdata1 and ib_logfile from mysql data directory
# /etc/init.d/mysql start

Solved MySQL replication after slaves's relay log was corrupted

mysql>show slave status\G;

Note
Relay_Master_Log_File: mysql-bin.002045
Exec_Master_Log_Pos: 1038350378

# stop slave
mysql> stop slave;

# make slave forget its replication position in the master's binary log
mysql> reset slave;

# change slave to start reading from stopped position
mysql> change master to master_log_file='mysql-bin.002045', master_log_pos=1038350378;

# start slave
mysql> start slave;

Backup and restore MySQL databases

Backup and restore MySQL databases
mysqldump -u root -p databasename > database.sql

Restore the mysql database using the mysql monitor
mysql -u  user_root -p db_name < sqldump.sql

Allow remote access to a mysql database

Allow remote access

# mysql -u admin -p
mysql>  GRANT ALL PRIVILEGES ON *.* to 'admin'@'%' IDENTIFIED BY 'abc_password'
mysql>  quit

Get record counts for all tables in MySQL database

mysql>SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name' GROUP BY TABLE_NAME;

 The COUNT(*) function returns the number of records in a table:

mysql> SELECT COUNT(*) FROM tiki_user_preferences;

Check basedir plugin_dir in mysql

Check basedir, plugin_dir etc in mysql

mysql -u root -p -h localhost

mysql> SHOW VARIABLES WHERE Variable_Name like "%dir%";
+-----------------------------------------+-------------------------------+
| Variable_name                           | Value                         |
+-----------------------------------------+-------------------------------+
| basedir                                 | /                             |
| binlog_direct_non_transactional_updates | OFF                           |
| character_sets_dir                      | /usr/share/mysql/charsets/    |
| datadir                                 | /storage1/mysqldata38/        |
| innodb_data_home_dir                    | /storage1/mysqldata38/ibdata1 |
| innodb_log_group_home_dir               | ./                            |
| innodb_max_dirty_pages_pct              | 90                            |
| plugin_dir                              | /usr/lib64/mysql/plugin       |
| slave_load_tmpdir                       | /tmp                          |
| tmpdir                                  | /tmp                          |
+-----------------------------------------+-------------------------------+
10 rows in set (0.00 sec)

MySQL Sphinx as Storage Engine installation on Centos

 cd /usr/local/src/

wget http://sphinxsearch.com/files/sphinx-2.2.10-release.tar.gz
wget https://downloads.mariadb.com/archives/mysql-5.1/mysql-5.1.59.tar.gz

tar -xzvf sphinx-2.2.10-release.tar.gz
tar -xzvf mysql-5.1.59.tar.gz

cp -R sphinx-2.2.10-release/mysqlse/ mysql-5.1.59/storage/sphinx

patch -p1 -i storage/sphinx/sphinx.5.0.91.diff
sh BUILD/autorun.sh

./configure
make

cd /usr/local/src/mysql-5.1.59/storage/sphinx/.libs
cp -aprv ha_sphinx* /usr/lib64/mysql/plugin/

mysql -u root -p -h localhost mysql> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| SPHINX     | YES     | Sphinx storage engine 2.2.10-release                           | NO           | NO   | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)


If you need to uninstall the sphinx plugin for some reason later on, this is how you do it:

mysql> UNINSTALL PLUGIN sphinx;

Save mysql query output to excel

>SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> SET PASSWORD = PASSWORD('root');
Query OK, 0 rows affected (0.00 sec)