Select Page

Author: SysAdminXpert

Fastest method for taking MySQL Backup and Restore

Mydumper and Myloader are utility which allows you to perform extremely fast and reliable multi-threaded MySQL backup and restore. These tools are written in the C programming language. Mydumper is a a tool for fast reliable logical backups. It is an alternative to mysqldump and has many advantages over mysqldump some of which are listed below: ·  Multi-threaded backup tool which makes it a lot faster than mysqldump, as mysqldump is single threaded. It will be more advantage if you have very fast storage such as SSDs which can be much better utilized with multiple threads. ·  The tool produces separate files for separate tables instead of one big monolithic file, making it easy to restore single tables. You can even break/chunk the table into multiple files which is super useful for cases where you have very large tables. ·  The tool allows for multi-threaded restores, making restores an order of magnitude faster in comparison to restoring from mysqldump produced backups. This is especially true for large datasets. ·  The Mydumper tool provides in-built compression, so that the backup files are written in compressed form. When it comes to backing up and restoring MySQL database, most people usually use the very popular mysqldump. You know that mysqldump is very easy to use for a smaller database, it doesn’t work well with larger databases. It’s very slow for huge databases and very error prone...

Read More

Setup Database and Create a Read-Only user in AWS Redshift and Mysql

 Even if you’re the only user of your data warehouse, it is not advised to use the root or admin password.  As a rule and as a precaution you should create additional credentials and a profile for any user that will have access to your DW.  Some systems provide an easier way of doing it than others. In the case of Amazon’s Redshift, it is not very straightforward.  Because sometimes, flexibility comes with complexity.  And that’s what we encountered when we tried to create a user with read-only access to a specific schema.  Here’s what you will need to achieve this task: Setup Database in Redshift  create database [db-name]; revoke all on schema public from public; create schema [schema-name]; create table [schema-name].[table-name](   … ) Create Read-only User in Redshift  create user [user-name] password ‘[password]’; grant usage on schema [schema-name] to [user-name]; alter user [user-name] set search_path to [schema-name]; grant select on table [schema-name].[table-name] to [user-name]; Refer: Amazon Redshift. http://awsdocs.s3.amazonaws.com/redshift/latest/redshift-dg.pdf Create read-only user on MySQL CREATE USER r_only_user identified by ‘password’; GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO ‘r_only_user’@’%’ IDENTIFIED BY ‘password’; GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO ‘r_only_user’@’localhost’ IDENTIFIED BY ‘password’; FLUSH...

Read More

Steps for installing Mysql-5.6 on linux

Install Steps Install dependencies sudo yum install ncurses-devel Download MySQL source code # cd /usr/local/src/ # wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.19.tar.gz # tar xvfz mysql-5.6.19.tar.gz # cd /usr/local/src/mysql-5.6.19 Build mysql # mkdir build # cd build # cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql # make # make install Initial Setup Create group and user # groupadd mysql # useradd -g mysql -d /usr/local/mysql mysql Create data directory # mkdir /var/lib/mysql # chown -R mysql:mysql /usr/local/mysql Modify /etc/my.cnf [client] socket=/tmp/mysql.sock [mysqld] datadir=/var/lib/mysql #socket=/var/lib/mysql/mysql.sock socket=/tmp/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd collation-server=utf8_unicode_ci character-set-server=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Create database # /usr/local/mysql/scripts/mysql_install_db –user=mysql –datadir=/var/lib/mysql -basedir=/usr/local/mysql Change owner # chown mysql:mysql -R /var/lib/mysql/* Copy initd script # cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld Retart mysql-server # /etc/init.d/mysqld restart Confirm $ mysql –ssl-ca ca-cert.pem mysql> show variables like ‘%ssl%’; +—————+———————————-+ | Variable_name | Value                            | +—————+———————————-+ | have_openssl  | YES                              | | have_ssl      | YES                              | | ssl_ca        | /usr/local/mysql/ca-cert.pem     | | ssl_capath    |                                  | | ssl_cert      | /usr/local/mysql/server-cert.pem | | ssl_cipher    |                                  | | ssl_crl       |                                  | | ssl_crlpath   |                                  | | ssl_key       | /usr/local/mysql/server-key.pem  | +—————+———————————-+ 9 rows in set (0.00 sec) mysql> show status like ‘Ssl_cipher’; +—————+——————–+...

Read More

AWS RDS Backup Methods

RDS provides two backup methods: Automated backups and user-initiated DB snapshots. Automated backups are initiated during the creation of an RDS instance. You set the backup window and the retention period for the backups and you’re ready. Although automated backups seem attractive because they are easy to manage, they have some constraints. 1.   Retention: There is a 35-day retention period limit. After exceeding that limit, the snapshot is then deleted. 2.   Deleted database: If you accidentally delete a database for any reason, automated backups are going to be removed too. 3.   Disaster Recovery: Automated backups can only be restored from within the same region. If you have a DR strategy, you might want to move the snapshots between multiple regions. These 3 constraints can be resolved with the DB snapshots. 1.   DB snapshots can be retained for as long as you wish. Means RDS manual snapshots will only be deleted when the admin specifically deletes the rds snapshots. 2.   DB snapshots are not removed if you accidentally delete the database. 3.   DB snapshots can be moved from one region to another without any constraint. Amazon Web Services gives us three ways to take manual DB Snapshots: the management console, the awscli command line utility and the various Amazon SDKs. As long as manual snapshots are taken regularly and kept secure, it’s easy to recover your database within a few minutes. Ultimately, you have...

Read More

How to install Nginx in RHEL and CentOS

NGINX is a free, open-source, high-performance HTTP server and reverse proxy, as well as an IMAP/POP3 proxy server. NGINX is known for its high performance, stability, rich feature set, simple configuration, and low resource consumption. To add NGINX yum repository, create a file named /etc/yum.repos.d/nginx.repo and paste one of the configurations below:             CentOS:                         [nginx]                         name=nginx repo                         baseurl=http://nginx.org/packages/centos/$releasever/$basearch/                         gpgcheck=0                         enabled=1             RHEL:                         [nginx]                         name=nginx repo                         baseurl=http://nginx.org/packages/rhel/$releasever/$basearch/                         gpgcheck=0                         enabled=1 Due to differences between how CentOS, RHEL, and Scientific Linux populate the $releasever variable, it is necessary to manually replace $releasever with either 5 (for 5.x) or 6 (for 6.x), depending upon your OS version. Installing nginx through yum is just a command away. and can be done as below: [root@app-node1 ~]# yum install nginx                                                                              Loaded plugins: rhnplugin, security This system is not registered with RHN. RHN support will be disabled. Setting up Install Process Parsing package install arguments Resolving Dependencies –> Running transaction check —> Package nginx.i386 0:0.8.55-2.el5 set to be updated –> Processing Dependency: libGeoIP.so.1 for package: nginx –> Running transaction check —> Package GeoIP.i386 0:1.4.8-1.el5 set to be updated –> Finished Dependency Resolution …. Dependencies Resolved ================================================================================  Package          Arch            Version                 Repository       Size ================================================================================ Installing:  nginx            i386            0.8.55-2.el5            epel            390 k Installing for dependencies:  GeoIP            i386            1.4.8-1.el5             epel            781 k Transaction Summary ================================================================================ Install      2 Package(s) Update       0 Package(s) Remove       0 Package(s) Total download size: 1.1 M Is this ok [y/N]: Y If you encounter dependency issues while installing through yum you can solve that by...

Read More