Latest revision as of 06:40, 20 April 2014

Phpmyadmin

You have to setup a provate password in the

vim /etc/phpmyadmin/settings.php

Search the blowfish section and add a password.

Backup

Basic usage:

mysqldump [--opt] -u USERNAME -p 'PASSWORD' \
    ( --all-databases | --databases DB_NAME_1 DB_NAME2 DBNAME_3 ... )

Secaurity issue: instead of using -p'PASSWORD' one should use the .my.cnf file and a [mysqldump] header.

The parameter -opt stands for:

--add-drop-table --add-locks --all --extended-insert --quick --lock-tables

pipe it to

| ssh user@server "dd of=/tmp/$(date +'%d-%m-%y').mysql.dump"

and you will dum directly to another server!

Replay:

mysql < mysql.dump

Attention:

If you don't use any params mysqldump will first load everything into ram. This can lead into problems when backing up big databases and/or less ram!

Links

mySqlDump Database Backup is a PHP script that is executed either on a scheduled basis or manually via your browser.
mysqldump - MySQL Referenzhandbuch (GER)

Reset root password

1. Stop mysql server

Via killall or on ubuntu via

kill `cat /var/run/mysqld/mysqld.pid`

or

/etc/init.d/mysql stop

2. Start server in special mode

You have to start mysql server in a special mode.

On some systems this can be done via

mysqld_safe-skip-grant-tables

On ubuntu/ debian

mysqld --skip-grant-tables &

3. Reset the password

mysql -u root
mysql> UPDATE user SET Password = PASSWORD ( 'newrootpassword') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

or login via phpmyadmin and do it there.

4. Restart the server

kill `cat /var/run/mysqld/mysqld.pid`
/etc/init.d/mysql start

or (ubuntu / debian)

 /etc/init.d/mysql restart

Difference between revisions of "MySQL"

From Blue-IT.org Wiki