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)