Difference between revisions of "MySQL"

From Blue-IT.org Wiki

(Backup)
 
(2 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

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