GenerationIP

Type : System
Operating System : Linux like, windows, BSD
Write by : Fabien FAYE

Mysql is one of the major opensource database and used in the enterprise solution. Mysql provide "master slave" replication solution or a cluster solution.

With mysql, you have a lot of engine definition for your table, like memory,innodb,myisam and BerkeleyDB. The last engine and one of the most interresting is the ndbcluster but this engine can be use only in a mysql cluster. In mysql 6 you can use falcon, this engine is created to replace innodb and improve the transaction methode for a database.

But here i want to show you a list of important commande line for mysql.

• Front END
• Mysql overview
• Commande line
• Backup
• Root password recovery
• TIPS

Front END

You have a lot of choice of front end but i proposed you this selection :

PHPMyAdmin : a reference in the opensouce world for the management of mysql with a web interface

Screenshot :

MySQL GUI Tools : this product is provide by mysql and can be install in windows, mac os and linux operating systems.

Screenshot :

Mysql overview

Mysql location :

/var/lib/mysql

Mysql Log :

/var/log/mysql

Mysql command:

mysql
mysql_config
mysql_convert_table_format
mysql_create_system_tables
mysql_explain_log
mysql_find_rows
mysql_fix_extensions
mysql_fix_privilege_tables
mysql_install_db
mysql_secure_installation
mysql_setpermission
mysql_tableinfo
mysql_tzinfo_to_sql
mysql_upgrade
mysql_waitpid
mysql_zap
mysqlaccess
mysqladmin
mysqlbinlog
mysqlbug
mysqlcheck
mysqld_multi
mysqld_safe
mysqldump
mysqldumpslow
mysqlhotcopy
mysqlimport
mysqlshow
mysqltest
mysqltestmanager
mysqltestmanager-pwgen
mysqltestmanagerc

Mysql File:

/var/lib/mysql/ibdata1 : for innodb

/var/lib/mysql/ib_logfile0 : for innodb

/var/lib/mysql/ib_logfile1 : for innodb

table.frm : structure

table.MYD : data

table.MYI : index

/etc/my.cnf : config file for mysqld

Command line

when you have install for the first time mysql you have to create a password for the root access.

Applied a password for the root :

#mysqladmin -u root password new_password

Now you have to create a database :

#mysqladmin -u root -p create database

After the creation of your database you have to create a user :

flush privileges is used for reload the rules and right of all users for each databases

#mysql -u root -p database
mysql>grant all on database.* to user@localhost identified by 'userpassword';
mysql>flush privileges;
mysql>exit

Now you have to create a table in your database:

mysql> use database
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (a), KEY(b))
-> ENGINE=MyISAM;

If you want to show which table you have in you database please use this command.

mysql>show tables;

If you want to restart, start or stop mysql you have to use thoses command line.

#/etc/init.d/mysql restart

#/etc/init.d/mysql start

#/etc/init.d/mysql stop

But you have also this way to stop mysql

#mysqladmin -u root -p shutdown

Backup

For the creation of a backup of your database you have the choice you can use a command line or backup-manager

If want to have a full backup you have to use this line

#mysqldump -u root -p --all-databases > mysql_backup-all_databases.sql

If you want to have a backupof a single database use this line.

#mysqldump -u root -p --databases db_name > mysql_db_name_backup.sql

But thoses backup are without locks, to have a full backup and sure you have to lock the export like that :

#mysqldump -u root -p --lock-all-tables --databases db_name > mysql_db_name_backup.sql

If you want to add a compression of your backup in the same commande line:

#mysqldump -u root -p --lock-all-tables --databases db_name | bzip2  > mysql_db_name_backup.sql

Now you have a collection of backup and you need to do a restauration, you have to do this command line.

#mysql -u root -proot < mysql_db_name_backup.sql

Root password recovery

For the recovery procedure of root password for mysql, you have to stop mysql.

#/etc/init.d/mysql stop

Now you have to start the mysqld_safe daemon without right

#mysqld_safe --skip-grant-tables &

After that you can establish a connection in root with out password and change the value in the mysql database and user table.

#mysql -u root
>use mysql;
>update user set password=PASSWORD("New-Password") where User='root';
>flush privileges;
>quit

Now you have to start mysql after a shutdown of mysqld_safe

#/etc/init.d/mysql start

TIPS

If you want an easy connection you have to edit a .my.cnf in your root folder or in your ssh user account.

example :

#cat /root/.my.cnf

[mysql]
password=root_password
[mysqldump]
password=root_password