Difference between revisions of "MySQL"

From Blue-IT.org Wiki

(Backup)
(Backup)
 
(5 intermediate revisions by the same user not shown)
Line 9: Line 9:
 
  mysqldump [--opt] -u USERNAME -p 'PASSWORD' \
 
  mysqldump [--opt] -u USERNAME -p 'PASSWORD' \
 
     ( --all-databases | --databases DB_NAME_1 DB_NAME2 DBNAME_3 ... )
 
     ( --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.
 
Secaurity issue: instead of using ''-p'PASSWORD''' one should use the ''.my.cnf'' file and a ''[mysqldump]'' header.
  
''-opt'' stands for:
+
The parameter '''-opt''' stands for:
 +
 
 
  --add-drop-table --add-locks --all --extended-insert --quick --lock-tables
 
  --add-drop-table --add-locks --all --extended-insert --quick --lock-tables
 +
 
pipe it to  
 
pipe it to  
 
  | ssh user@server "dd of=/tmp/$(date +'%d-%m-%y').mysql.dump"
 
  | ssh user@server "dd of=/tmp/$(date +'%d-%m-%y').mysql.dump"
 +
 +
and you will dum directly to another server!
 +
 
Replay:
 
Replay:
 
  mysql < mysql.dump
 
  mysql < mysql.dump
Line 24: Line 30:
 
* [http://www.scripts.oldguy.us/mysqldump/ mySqlDump Database Backup] is a PHP script that is executed either on a scheduled basis or manually via your browser.
 
* [http://www.scripts.oldguy.us/mysqldump/ mySqlDump Database Backup] is a PHP script that is executed either on a scheduled basis or manually via your browser.
 
* [http://dev.mysql.com/doc/refman/5.1/de/mysqldump.html mysqldump - MySQL Referenzhandbuch (GER)]
 
* [http://dev.mysql.com/doc/refman/5.1/de/mysqldump.html 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
 +
 +
[[Category:Database]]

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

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