Connect to MySQL

Get the db credentials from /etc/mysql/debian.cnf
Run mysql -u debian-sys-maint -p
mysql> show databases ;
mysql> use observer_test;
show tables ;

-> Create a new user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'obs3-meta'@'localhost' IDENTIFIED BY 'obs3-meta';
Query OK, 0 rows affected (0.00 sec)

-> Create a new database:

mysql> create database obs3-meta ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-meta' at line 1
mysql> create database obs3_meta ;
Query OK, 1 row affected (0.00 sec)

mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| obs3_meta          |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use obs3_meta;
Database changed
mysql> show tables ;
Empty set (0.00 sec)

-> Restore data from a dump file

mysql> source observer_back_up.20180716;

-> Create a user and grant permissions to a db

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL ON my_db.* TO 'new_user'@'localhost';

or 

mysql> GRANT USAGE ON *.* to 'username'@'localhost' identified by 'username';

-> To display the a table definition from the db (This can also be used in .sql file)

mysql> show create table <table_name>;

-> Sample .sql file

CREATE <database_name> ;

USE `<database_name>` ;

CREATE TABLE `example` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `hostname` varchar(30) NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=8376 DEFAULT CHARSET=latin1 ;

Blog

Santhosh Balasa

Join Our Newsletter. No Spam, Only the good stuff.

Related