MySQL Tutorial
Display Help and Version Information
shell> mysql --help
or
shell> mysql -?
^ Top
Connect to MySQL
Some MySQL installations allow users to connect as the anonymous (unnamed) user to the server running on the local host. If this is the case on your machine, you should be able to connect to that server by invoking mysql without any options:
shell> mysql
Standard syntax :
shell> mysql -h host -u user -p
Enter password: ********
If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply use the following:
shell> mysql -u user -p
default installation : hanya ada satu user yaitu root dimana root ini adl system administrator yg mempunyai hak tak terbatas terhadap MySQL.
^ Top
Display databases in MySQL instance
mysql> show databases;
^ Top Open a database USE db_name
The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued:
^ Top Display tables in a database mysql> show tables;
^ Top First Time MySQL Instalasi default MySQL akan membuat satu user account yaitu root. Pastikan hanya ada satu user account pada MySQL, yaitu root. Pastikan user root sebagai system administrator mempunyai akses (
privileges) tak terbatas. Data user tersimpan di database
mysql tabel
user.
mysql> use mysql
mysql> select * from user \G
Untuk menampilkan privileges dari user gunakan GRANT
SHOW GRANTS FOR
user mysql>
SHOW GRANTS FOR 'root'@'localhost'; To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
Sebelum menggunakan MySQL untuk membuat database, ubah dahulu password dari root.
mysql> SET PASSWORD = PASSWORD('
new_password');
^ Top Miscellaneous Functions To find out which database is currently selected, use the DATABASE() function:
mysql> SELECT DATABASE();
A simple command that asks the server to tell you its version number :
mysql> SELECT VERSION();
Server current date :
mysql> SELECT CURRENT_DATE;
If you want to find out about the structure of a table, the DESCRIBE command is useful; it displays information about each of a table's columns:
mysql> DESCRIBE table_name;
Displays status information about the server's storage engines :
mysql> SHOW ENGINES \G
Displays extensive information about the state of the
InnoDB storage engine :
mysql>
SHOW ENGINE INNODB STATUS; Display information including storage engine used for table(s) :
mysql> SHOW TABLE STATUS;
^ Top
Backing Up and Recovering an InnoDB Database Binary Backup If you are able to shut down your MySQL server, you can make a binary backup that consists of all files used by InnoDB to manage its tables. Use the following procedure:
- Shut down your MySQL server and make sure that it shuts down without errors.
- Copy all your data files (ibdata files and .ibd files) into a safe place.
- Copy all your ib_logfile files to a safe place.
- Copy your my.cnf configuration file or files to a safe place.
- Copy all the .frm files for your InnoDB tables to a safe place.
To recover your database, just shut down your MySQL server and copy this binary backup files back to it's original place.
To be able to recover your InnoDB database to the present from the binary backup just described, you have to run your MySQL server with binary logging turned on. Then you can apply the binary log to the backup database to achieve point-in-time recovery:
mysqlbinlog yourhostname-bin.123 | mysql
where
yourhostname-bin.123 is binary log file(s) name
mysqldump Use mysqldump for regular database backup
The most common use of mysqldump is probably for making a backup of an entire database:
shell> mysqldump --opt db_name > backup_file_name.sql
or for making a back up of all databases:
shell> mysqldump --all-databases --single-transaction >
backup_file_name.sql
To add option user, password and host name:
shell> mysqldump --all-databases --single-transaction --user=root --host=localhost -p >
backup_file_name.sql
you will be prompted for password
To flush the binary log and write the name of the new current binary log in to the dump file:
shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >
backup_file_name.sql
To purge old binary log files that are no longer needed:
shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup_file_name.sql
You can read the dump file back into the server (recovery or restore) like this:
shell> mysql db_name < backup_file_name.sql
or to restore from dump file that contain all databases:
shell> mysql <
backup_file_name.sql
To add option user, password and host name:
shell> mysql -u root -p -h localhost <
backup_file_name.sql
you will be prompted for password
If you want to restore incremental changes (from binary log file(s)) execute this:
shell> mysqlbinlog
binary_log_file-bin.000001 binary_log_file-bin.000002 | mysql
where binary_log_file-bin.000001 and binary_log_file-bin.000002 are binary log file names.
^ Top
Table Operation To change column type:
ALTER TABLE
table_name MODIFY
column_name new_column_type;
To rename a column:
ALTER TABLE
table_name CHANGE
old_name new_name column_type;
^ Top
![[info]](http://l-stat.livejournal.com/img/userinfo.gif?v=92.1){kind=small}
energetic