Advertisement
Guest User

Untitled

a guest
Apr 12th, 2016
113
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.05 KB | None | 0 0
  1. mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword
  2.  
  3. mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver
  4.  
  5. [mysqld]
  6. lower_case_table_names=1
  7.  
  8. LOCK TABLES mydb.mytable READ;
  9. SELECT SLEEP(86400);
  10. UNLOCK TABLES;
  11.  
  12. mysql -u username -ppassword databasename < databasename.tablename.sql
  13.  
  14. me@web:~$ d=members
  15. me@web:~$ mysqldump $d | gzip > $d.sql.gz
  16. me@web:~$ scp -i .ssh/yourkeynamehere $d.sql.gz $sbox:$d.sql.gz
  17.  
  18. me@sandbox:~$ d1=members
  19. me@sandbox:~$ d2=members_sb
  20. me@sandbox:~$ mysqladmin create $d2
  21. me@sandbox:~$ cat $d1.sql.gz | gunzip | mysql $d2
  22.  
  23. me@sandbox:~$ ls *.gz
  24. me@sandbox:~$ cat $d.sql.gz | gunzip | less
  25.  
  26. $ echo "
  27. [client]
  28. user=drupal6
  29. password=metoknow
  30. host=ord-mysql-001-sn.bananas.com
  31. [mysql]
  32. database=nz_drupal" > .my.cnf
  33. $ chmod 0600 ~/.my.cnf
  34.  
  35. # mysqldump -u username -ppassword database_name > FILE.sql
  36.  
  37. /etc/init.d/mysqld stop
  38. rsync -avz source_files destination
  39. vi /etc/my.cnf
  40.  
  41. /etc/init.d/mysql start
  42.  
  43. sudo cp /var/lib/mysql /home/foouser/mysql_data_folder
  44. sudo chown -R foouser /home/foouser/mysql_data_folder
  45.  
  46. sudo chown -R _mysql /usr/local/mysql/data/
  47. sudo chgrp -R wheel /usr/local/mysql/data/
  48. sudo chmod -R g+rx /usr/local/mysql/data/
  49.  
  50. ssh server1
  51. service mysql stop
  52. cd $mysql-data-dir
  53. rsync -avz dirs-or-files server2:$mysql-data-dir
  54. service mysql start
  55.  
  56. ssh server1
  57. mysqldump ... | pigz > backup-YYMDD.sql.gz
  58. rsync backup-YYMDD.sql.gz server:location
  59.  
  60. ssh server2
  61. pigz -dc location/backup-YYMDD.sql.gz | mysql ..
  62.  
  63. In HA environment you should use the following trick:
  64. setup slave server & do all backups from it
  65. before backups - do "slave stop";
  66. then do version 1 or version 2
  67.  
  68. touch full.start
  69. mysqladmin -h slave-db stop-slave
  70. echo "show slave status G" | mysql -h slave-db > FULL/comfi-$NOW.master-position
  71. /usr/bin/mysqldump -h slave-db --default-character-set=utf8 -A --opt --skip-lock-tables | pigz > "FULL/XXXX-$NOW.sql.gz"
  72. mysqladmin -h slave-db start-slave
  73. touch full.end
  74.  
  75. ln -fs "FULL/XXXX-$NOW.sql.gz" FULL.sql.gz
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement