https://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html
to connect mysql, it will prompt for password
mysql -u root -p
http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm
show databases;
use db_name
show tables;
select * from table_name
to take backup of all databases:-
mysqldump -u root -p --all-databases > all_database.sql
to restore only one database from all databse backup
mysql -u root -p racktables < all_database.sql
to restore only one database from one databse backup
mysql -u root -p racktables < ./racktables.db-Tue-17-10-2017:03:00:01.sql
-------------------------------------------------------------------------
Path of data directory
mysql> show variables where Variable_Name LIKE "%dir"
----------------------------------------------
Create new database and provide priviledges:-
CREATE DATABASE old_racktables_db CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER oldrackuser@localhost ;
GRANT ALL PRIVILEGES ON old_racktables_db.* TO oldrackuser@localhost;
flush privileges;
View privileges on database:-
show grants for oldrackuser@localhost
import databse:-
mysql -u root -p old_racktables_db < ./racktables.db-Sun-10-12-2017\:22\:26\:34_new.sql
____________________________________________________________
MySQL Maintenance Task
Backup database
Check
Optimize
Analyze
$ mysqldump -u root -p --create-options --routines --triggers dbname > ./db.dmp
# note these cause LOCKS, so be careful on your production server!
$ mysqlcheck -u root -p --check --databases dbname
$ mysqlcheck -u root -p --optimize --databases dbname
$ mysqlcheck -u root -p --analyze --databases dbname
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
In Case Importing Database to same system or another system but USER Name and DATABSE NAME are different than the original, then need to edit the SQL dump file useing vi editor and modify the old databse name and the User Name in the dump before import. Just search the old database name and modify with the new database name and same for the user name too. Other wise you will receive below Error
Error similar to :- SQLSTATE[HY000]: General error: 1356 View 'racktables.RackObject' references
invalid table(s) or column(s) or function(s) or definer/invoker of view lack
rights to use them (HY000)
Ref:-
https://www.freelists.org/post/racktables-users/export-import-racktable-data-from-old-database-to-new-database,2
Hi.
If I remember correctly I had similar errors one time when I was exporting the
Racktables database and importing it to another database on the same server
with a different database username.
I had to edit the SQL dump files before I imported them to the new database.
The dump had references to the old database name and username. Take a look at
lines with "DEFINER" and "ALTER" in the sql dump.
Hope this helps,
____________________________________________________________________
