mysql setting backup

By OK -

Setting backups

In this recipe, we will learn how to back up the MySQL database.

Getting ready

You will need administrative access to the MySQL database.

How to do it…

Follow these steps to set up the backups:
  1. Backing up the MySQL database is the same as exporting data from the server. Use the mysqldump tool to back up the MySQL database as follows:
    $ mysqldump -h localhost -u admin -p mydb > mydb_backup.sql
  2. You will be prompted for the admin account password. After providing the password, the backup process will take time depending on the size of the database.
  3. To back up all databases, add the --all-databases flag to the preceding command:
    $ mysqldump --all-databases -u admin -p alldb_backup.sql
  4. Next, we can restore the backup created with the mysqldump tool with the following command:
    $ mysqladmin -u admin -p create mydb
$ mysql -h localhost -u admin -p mydb < mydb_backup.sql
  5. To restore all databases, skip the database creation part:
    $ mysql -h localhost -u admin -p < alldb_backup.sql

How it works…

MySQL provides a very general tool, mysqldump, to export all data from the database server. This tool can be used with any type of database engine, be it MyISAM or InnoDB or any other. To perform an online backup of InnoDB tables, mysqldump provides the --single-transaction option. With this option set, InnoDB tables will not be locked and will be available to other applications while backup is in progress.
Oracle provides the MySQL Enterprise backup tool for MySQL Enterprise edition users. This tool includes features such as incremental and compressed backups. Alternatively, Percona provides an open source utility known as Xtrabackup. It provides incremental and compressed backups and many more features.
Some other backup methods include copying MySQL table files and the mysqlhotcopy script for InnoDB tables. For these methods to work, you may need to pause or stop the MySQL server before backup.
You can also enable replication to mirror all data to the other server. It is a mechanism to maintain multiple copies of data by automatically copying data from one system to another. In this case, the primary server is called Master and the secondary server is called Slave. This type of configuration is known as Master-Slave replication. Generally, applications communicate with the Master server for all read and write requests. The Slave is used as a backup if the Master goes down. Many times, the Master-Slave configuration is used to load balance database queries by routing all read requests to the Slave server and write requests to the Master server. Replication can also be configured in Master-Master mode, where both servers receive read-write requests from clients.

See also

Comments

Post a Comment

Popular posts from this blog

HAproxy logging

By OK -
ubuntu@ubuntu:/etc/haproxy$ cat /etc/rsyslog.d/haproxy.conf # Create an additional socket in haproxy's chroot in order to allow logging via # /dev/log to chroot'ed HAProxy processes $ModLoad imudp $UDPServerRun 514 local0.* -/var/log/haproxy-0.log local1.* -/var/log/haproxy-1.log ### keep logs in localhost ## $AddUnixListenSocket /var/lib/haproxy/dev/log ------------------- ubuntu@ubuntu:/etc/haproxy$ cat /etc/haproxy/haproxy.cfg global chroot /var/lib/haproxy user haproxy group haproxy log 127.0.0.1   local0         log 127.0.0.1   local1 notice daemon frontend www     bind 192.168.40.128:8282    # haproxy public IP     default_backend as-backend    # backend used backend as-backend    balance leastconn    mode http  server as1 192.168.40.128:8082 check    # application srv 1 defaults log ...
Read more

teamcity Automatic Agent Start under Linux

By OK -
Automatic Agent Start under Linux To run agent automatically on the machine boot under Linux, configure daemon process with the  agent.sh start  command to start it and  agent.sh stop  command to stop it. Refer to an example procedure below: 1. Navigate to the services start/stop services scripts directory: cd /etc/init.d/ 2. Open the build agent service script: sudo vim buildAgent 3. Paste the following into the file : #!/bin/sh ### BEGIN INIT INFO # Provides: TeamCity Build Agent # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start build agent daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO #Provide the correct user name: USER="agentuser" case "$1" in start) su - $USER -c "cd BuildAgent/bin ; ./agent.sh start" ;; stop) su - $USER -c "cd BuildAgent/bin ; ./agent....
Read more

NFS mount add in fstab _netdev instead of default | firewall-cmd --list-all

By OK -
Redhat recommends us to add  _netdev  option for NFS file system in /etc/fstab. So that they can be mounted after starting the networking service. Alternative ways to refer to partitions: Label : LABEL=label Network ID Samba : //server/share NFS : server:/share SSHFS : sshfs#user@server:/share Device : /dev/sdxy (not recommended) Example #1 //192.168.1.134/share_name /mnt/share_name cifs username=server_user,password=server_password,_netdev 0 0 ^ Path to your share ^ Mountpoint ^ Username ^ Password ^ See note A Note A: The  _netdev  options makes sure the mount is only attempted AFTER a network connection has been established. Example #2 10.10.10.1:/vol1/fs1 /data nfs defaults,_netdev 0 0 ============================ [root@localhost ~]# firewall-cmd --list-all public   target: default   icmp-block-inversion: no   interfaces:   sources:   services: dhcpv6-client high-availabi...
Read more