HostOnNet Blog

Category Archives: MySQL

Secure MySQL Installation with mysql_secure_installation

On Ubuntu 16.04, after MySQL (MariaDB) installed, you have no root password. Root is able to login with out any password. To set root password for MySQL, after installation, run You will be asked to set new password, also allow


MySQL Show all Supported Database Engines

To list all supported Database Engines in MySQL run show engines; Example mysql> show engines; +——————–+———+—————————————————————————-+————–+——+————+ | Engine | Support | Comment | Transactions | XA | Savepoints | +——————–+———+—————————————————————————-+————–+——+————+ | CSV | YES | CSV storage engine | NO


Storing MySQL password in .my.cnf

Create ~/.my.cnf file touch ~/.my.cnf chmod 700 ~/.my.cnf Edit file vi ~/.my.cnf Add following content Now you will be able to login to server with out password.


Reset MySQL root password

To reset MySQL root password, first stop MySQL server Stop MySQL Server systemctl stop mariadb If that did not work, one of the following command may work for you. service mysql stop service mariadb stop systemctl stop mysql Start MySQL


MariaDB Too many connections

On CentOS 7 server using MariaDB 10.1.21 site stopped working with following error. This is because by default MySQL only allow 151 connections To fix, edit file Add max_connections = 500 under [mysqld] section. Now restart MySQL Verify max_connections limit


mysqladmin

mysqladmin is a commandline tool provided as part of MySQL. To list MySQL run time variables, run To see value of open_files_limit, use grep with mysqladmin, for example


Change MySQL Root Password

To change MySQL root Password, login to MySQL as user root. Now run


Create MySQL User

To create a MySQL user, run GRANT ALL ON DB_NAME_HERE.* TO ‘USER_NAME_HERE’@’localhost’ IDENTIFIED BY ‘PASSWORD_HERE’; Allow Access to all databases If you want to allow new user to have access to all database, run GRANT ALL ON *.* TO ‘USER_NAME_HERE’@’localhost’


ERROR 1286 (42000): Unknown storage engine ‘InnoDB’

After upgrading MySQL to MariaDB 10.0.23, i got following error This was because InnoDB was not enabled in /etc/my.cnf To enable InooDB, edit add Now restart MariaDB, on CentOS 7 server, run


mysqldump

mysqldump command is used to take MySQL backup. Take Backup of All databases To take backup of all Databases on a MySQL server, run Take Backup of a MySQL Database To take Backup of a MySQL Database, run