mysql notes

need /etc/my.cnf
simply link one of the sample files to it

-------------
line may be needed:
user = 
user such as root
two users may be needed, see example my.cnf file

or
/usr/bin/mysqld_safe --user=root

but this seems to require a user and password
so leave out these two user fields for anonymous usage
-------------

then:
su - mysql
mysql_installdb
exit

####################
don't do this: set password:
/usr/bin/mysqladmin -u root password "mypassword blah...."

don't do this: change password with -p:
/usr/bin/mysqladmin -u root -pmyOldPassword password 'my new password'

don't do this: test it:
mysqladmin -u root -pmyPassword create addressbook
#####################

to login mysql
mysql -u root -p
then enter password when prompted


---------------------------------

mysql database backup


ON THE SAME MACHINE:

Backup:
mysqldump -u joeBlow -p  myDatabase > myArchive.ext

Restore:
mysql -u joeBlow -p myDatabase < myArchive.ext



IF ON TWO DIFFERENT MACHINES

Backup:
Same as above

Restore (or create):
If the database does not exist:
mysql -u joeBlow -p
create database myDatabase;
then, same step as above.




misc backup notes:
Select multiple databases:
mysqldump -u joeBlow -p --databases myDatabase1 myDatabase2 myDatabase3 > myArchive.ext

backup all databases:
mysqldump --all-databases > alldatabases.sql



Recover tables from backuped up files:
if files for database 'myDatabase' in /var/lib/mysql/myDatabase were copied to /myBackups/myDatabase
and a recovery is needed:
each table contains three files: .frm, .MYI, .MYD
So for table myBigTable we get these files: myBigTable.frm, myBigTable.MYI, myBigTable.MYD
To recover table, just copy the three archived files.


copy database or import database:
from a mysqldump file myBigDatabase.sql:
mysql -u  -p
create database myBigDatabase;
use myBigDatabase;
source /myPath/myBigDatabase.sql;


--------------------------
basic commands


show databases;

show tables;

create database hello;

create table MyCrayons ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100), cur_timestamp TIMESTAMP(8) );

drop database hello;

drop table table1;