MySQL Tutorials

Replication

Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated.

Replication allows you to take one database, make an exact copy of it on another server, and set one of them (the slave) to take all its updates from the other (the master).

http://www.howtoforge.com/mysql_database_replication

Configure master

¨ In your my.ini file put these lines:

[mysqld]

log-bin = allen_binary

binlog-do-db=exampledb

server-id=1

log-bin indicates that the master will use binary update logging, binlog-do-db indicates which database we will use and server-id is a unique number to differentiate each of the master and slave machines. By convention, the master is usually set to 1, and the slaves from 2 upward:

¨ Now in the mysql shell, we will grant a permission to a slave to replicate. The slave user will be replication_user, with a password of replication_pwd:

• GRANT REPLICATION SLAVE ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';

• GRANT RELOAD ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';

• GRANT SUPER ON *.* TO replication_user IDENTIFIED BY 'replication_pwd';

¨ You can also type this

>FLUSH privileges

Once you have given the desired privileges for your user, you will need to FLUSH privileges in order to complete the setup and to make the new settings work.

¨ Then use the database you want to replicate

>use replicationdb;

>show master status;

¨ Then leave the MySQL shell:

>quit;

¨ master-host = 192.168.4.100

¨ master-user = replication_user

¨ master_password = replication_pwd

¨ server-id = 3

¨ master-connect-retry=60

¨ replicate-do-db = replication_db

There are two possibilities to get existing tables and data from replicationdbfrom master to slave

¨ make a database dump

mysqldump -u root -proot -- opt replicationdb> C:replicationdb.sql

Transfer this file to your slave server

¨ load data from master

If you want to go the LOAD DATA FROM MASTER;

¨ Now we will configure the slave:

¨ First create database replicationdb then quit mysql by “quit” command

¨ In the my.ini or my.cfg file, put these lines

Replace the master-host setting with the actual IP address of the master server, Master-user is the user created on the master for replication as well as the master-password. The server_id can be any number, as long as it's not the same as the server_id on the master:

¨ Then quit and copy the sql

¨ Then on the mysql shell copy the replicationdb data from the master to the slave

>mysql -u root replicationdb< C:replicationdb.sql

¨ Then we restart MySQL

If you have not imported the master replicationdb with the help of an SQL dump, then lets use the LOAD DATA FROM MASTER;

>mysql -u root

>LOAD DATA FROM MASTER;

>quit;

*If you have phpMyAdmin installed on the slave you can now check if all tables/data from the master replicationdb is also available on the slave replicationdb.

¨ Next step is to stop the slave to finish up the Slave configuration.

mysql -u root -p
Enter password:
SLAVE STOP;

¨ replace the values appropriately:

>CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER=‘replication_user', MASTER_PASSWORD='replication_pwd', MASTER_LOG_FILE=‘allen_binary.000001', MASTER_LOG_POS=106;

¨ MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).

¨ MASTER_USER is the user we granted replication privileges on the master.

¨ MASTER_PASSWORD is the actual password gave to the user on the master.

¨ MASTER_LOG_FILE is the file MySQL gave, back when you ran SHOW MASTER STATUS; on the master.

¨ MASTER_LOG_POS is the position MySQL gave back when you also ran SHOW MASTER STATUS; on the master.

¨ Then start the slave.

>START SLAVE;

And now, be aware that if your master server has already been using binary logging, you'll need to reset the binary log with RESETMASTER so that the slave can start updating from the beginning of the first binary log. Now start the slave server and connect. Once you've connected, check the slave status to see if replication has begun properly:

Configuring Master and Slave Files

Master Configuration File Options

¨ Slave Configuration File Options

Master Configuration File Options

¨ log-bin=filename

¡ Activates binary logging. This option must be present on the master for replication to occur. To clear the log, run RESET MASTER, and do not forget to run RESET SLAVE on all slaves.

¡ log-bin-index=filename

¡ Specifies the name of the binary log index file (which lists the binary log files in order, so the slave will always know the active one). The default is hostname.index

¨ sql-bin-update-same

¡ If set, setting SQL_LOG_BIN to either 1 or 0 will automatically set SQL_ LOG_UPDATE to the same value, and vice versa. SQL_LOG_UPDATE should not be needed any more, so this option is unlikely to be used.

¡ binlog-do-db=database_name

¡ Only logs updates to the binary log from the database_name database. All other databases are ignored.

¨ binlog-ignore-db= database_name

¡ Logs all updates to the binary log except from the database_name database. You can also set the database to ignore on the slave.

¨ master-host=host

¡ Specifies the hostname or IP address of the master to which to connect. Has to be set for replication to begin. Once replication has begun, the master.info data will determine this, and you'll need to use a CHANGE MASTER TO statement to change it.

¡ master-user=username

¡ Specifies the username the slave will connect to the master with. The user should have REPLICATION SLAVE permission on the master.

¡ master-password=password

¡ Specifies the password with which the slave will connect to the master.

¨ master-port=portnumber

¡ Specifies the port the master listens on (defaults to the value of MYSQL_ PORT, usually 3306).

¨ master-connect-retry= seconds

¡ If the connection between the master and slave goes down, MySQL will wait this many seconds before trying to reconnect (default 60).

¡ master-ssl

¡ Specifies that replication take place using Secure Sockets Layer (SSL).

¨ master-ssl-key=key_name

¡ If SSL is set to be used (the master-ssl option), this specifies the master SSL key filename.

¡ master-ssl-cert= certificate_name

¡ If SSL is set to be used (the master-ssl option), this specifies the master SSL certificate name.

¡ master-info-file=filename

¡ Specifies the master information file (default master.info in the data directory), which keeps track of the point in the binary logs that the slave is at in the replication process.

Slave Configuration File Options

¨ report-host

¡ Specifies the hostname or IP address that the slave will announce itself as to the master (for use during a SHOW SLAVE HOSTS statement). Not set by default. Other methods of determining the host are not reliable; hence the need for this option.

¡ report-port

¡ Specifies the port for connecting to the slave reported to the master. You should only need this if the slave is on a nondefault port, or connection takes place in a nonstandard way.

¡ replicate-do-table= db_name.table_name

Ensures that the slave only replicates the specified table name, from the specified database. You can use this option multiple times to replicate multiple tables

¨ replicate-ignore-table= db_name.table_name

¡ Tells the slave not to replicate a statement that updates the specified table (even if other tables are also updated by the same statement). You can specify multiple instances of this option.

¨ replicate-wild-do-table= db_name.table_name

¡ Tells the slave to replicate statements only where they match the specified table (similar to the replicate_do_table option), but where the matchtakes into account wildcards. For example, where the table name isdb%.tb%, the match will apply to any database beginning with the letters db, and any table beginning with the letters tb.

¨ replicate-wild-ignore-table=db_ name.table_name

¡ Tells the slave not to replicate a statement that updates the specified table, even if other tables are also updated by the same statement, similar to the replicate-ignore-table option, except that wildcards are taken into account. For example, where the table name is db%.tb% replication will not be performed where the database begins with the letters db, and the table begins with the letters tb). You can specify multiple instances of this option.

¨ replicate-ignore-db= database_name

¡ Tells the slave not to replicate any statement when the current database is database_name. You can use this option multiple times to specify multiple databases to ignore.

¨ replicate-do-db= database_name

¡ Tells the slave thread to replicate a statement only when the database is database_name. You can use this option multiple times to replicate multiple databases.

¨ log-slave-updates

¡ Tells the slave to log replicated updates to the binary log. Not set by default. If you plan to use the slave as a master to another slave, you'll need to set this option.

¨ replicate-rewrite-db= master_database->slave_database

¡ If the database on your slave has a different name to that on the master, you'll need to map the relationship with this option.

¨ slave-skip-errors= [err_code1,err_ code2,... | all]

¡ When replication encounters an error, it will stop (since an error usually means the data is inconsistent, and manual steps are needed). This option tells MySQL to continue replicating if the error is one of the listed errors. Error codes are supplied as a number (the same number given in the error log) and separated by a comma. You can also use the all option to cater for any possible errors. You should not normally use this option, as mistaken use of it can lead to your data getting out of sync with the master, with no realistic way of getting it back in sync besides recopying the master data.

¨ skip-slave-start

¡ With this option set, replication will not begin when the server starts. You can manually begin it with the SLAVE START command.

¨ slave_compressed_protocol=#

¡ If set to 1, then MySQL uses compression to transfer the data between slave and master if both servers support this.

¨ slave_net_timeout=#

¡ Determines the number of seconds to wait for more data from the master before a read is aborted.

Slave Replication Commands

¨ start and stop the replication process, respectively.

¨ returns information about the slave, including the important fact whether the slave is connected to the master (Slave_IO_Running), replication is running (Slave_SQL_Running), what binary log is being used (Master_Log_File and Relay_Master_Log_ File), and what position is current in the binary log (Read_Master_Log_Pos and Exec_ master_log_pos).

¨ This statement is an important one for keeping the replication in sync or starting it off at the right place. The MASTER_LOG_FILE refers to the binary log on the master from which the slave must start replicating, and the MASTER_LOG_POS is the position in that file. (You'll see examples of this later in the chapter.) This statement is also used when the master fails, and you need to change the master to which the slave connects. The full set of CHANGE MASTER TO options is:

¨ CHANGE MASTER TO MASTER_HOST = 'master_hostname', MASTER_USER='replication_username', MASTER_PASSWORD=''replication_user_password', MASTER_PORT='master_port', MASTER_LOG_FILE='master_binary_logfile', MASTER_LOG_POS='master_binary_log_position'

LOAD DATA FROM MASTER

takes a copy of the data on the master and brings it onto the slave. Currently, this is not useful for large datasets or for situations where the master cannot be unavailable for long, as it acquires a global read lock when copying the data. It also updates the value of MASTER_LOG_FILE and MASTER_LOG_POS. Currently it only works with MyISAM tables. This statement is likely to become the standard way of preparing the slave in future, so be sure to read your latest documentation.

In this Tutorial discuss different ways and steps how back up and make you databases safe.

1.Backup by Directly Copying Files

Backing up

Backing up databases can also be done by directly copying files.

This can work with MyISAM tables and only applicable in this table.

MyISAM tables

MyISAm tables are stored as files

.MYD

.MYI

.FRM

Lock and Flush

Does not directly lock tables unlike on backup

flush the tables to make sure any unwritten indexes are written to disk

In copying files we need to open two(2) windows:

Window1:

LOCK TABLES table_name READ,table_name READ,…..READ;

FLUSH TABLES table_name , table_name 2…;

Directly Copying Files:

On Window2:

C:\mydata\data\db>copy table_name.* C:\backup

Data directory table to copy backup path

Example:

After copying, release the table using unlock statement on window1:

Mysql>UNLOCK TABLES;

To test the backup, you're going to drop a table.

2.DATABASE BACKUP using select QUeries

Select form that enables a query to be written to a file

select ...into :

select … into outfile

Writes a selected row to a file

Syntax:

SELECT … INTO OUTFILE ‘file_name’ fields terminated by ‘terminatot’ from [table_name];

select … into dumpfile

select … into var_list

Load Data Infile

It is use to read the file back to the table

Syntax:

LOAD DATA INFILE [‘file_name’] into table [name_of_table];

If the data inside the data.txt looks like this:

Solution:

LOAD DATA INFILE [‘file_name’] into table [name_of_table] lines terminated by [‘terminator’];

Skipping over a prefix:

LOAD DATA INFILE [‘file_name’] into table [name_of_table] fields terminated by [‘terminator’] lines starting by [‘starting_value’];

3. BACKUP and RESTORE commands

What is Backup?

The purpose of backup is to recover data from an earlier time.

Syntax:

BACKUP TABLE [table_name] to ‘[path]’;

Example:

mysql> BACKUP TABLE Department TO ‘d:\\backup_folder';

What is Restore?

A restore is performed in order to return data to its original condition if files have become damaged, or to copy or move data to a new location.

Syntax:

RESTORE TABLE [table_name] from ‘[path]’;

Example:

RESTORE TABLE Department FROM ‘d:\\backup_folder';

Other syntax:

To backup using mysqldump:

Mysqldump –u root –p[password] [database_name] > [path];

To restore:

Mysql –u root –p [password] [database_name] < [path];

4. Mysqldump

The Syntax:

mysql\bin> mysqldump [arguments] > file_name;

• Dump all database

mysql\bin> mysqldump --all-databases > dump.sql

• Dump specific databases

mysql\bin> mysqldump -u root --databases db1 db2 db3 > dump.sql

• Dump single database

mysql\bin> mysqldump -u root --databases clsu > dump.sql

• OK omit --databases in single database

mysql\bin> mysqldump -u root clsu > dump.sql

• Dump specific table

mysql\bin > mysqldump -u root clsu college student> dump.sql

mysqldump

• If dump with --databases create dump with create database and use

• If without --databases create dump without create databases and use

• When reloading dump file specify database name

• Or specify different name from original

• If no database exist create first

Mysqlhotcopy

• Runs only on Unix and NetWare

• Used for bucking up MyISAM and ARCHIVE

• Must have select privilege, reload privilege, and lock tables privilege

• shell> mysqlhotcopy db_name [/path/to/new_directory]

• shell> mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

Backing Innodb

• Two way using mysqldump and copy the file

• Too copy file shutdown MySQL server make sure no errors

• Copy all InnoDB data file (ibdata file and .ibd file) to safe place

• Copy all .frm file

• Copy all InnoDB log file

• Copy you’re my.cnf configuration

Recover Innodb

• Run Mysql server with binary log on defore taking the buckup

• To achieve point-in-time recovery you can apply changes from binary log

• To recover from mysql crash just restart it and the Innodb automatically logs and perform rolls back up to present

Dump all database

a. mysql\bin> mysqldump --all-databases > dump.sql

b. mysql\bin> mysqldump –u root --all-databases > dump.sql

c. mysql\bin> mysqldump --all > dump.sql

Dump specific databases

a. mysql\bin> mysqldump --databases db1 db2 db3 > dump.sql

b. mysql\bin> mysqldump -u root --databases db1 db2 db3 > dump.sql

c. mysql\bin> mysqldump db1 db2 db3 > dump.sql

Dump single database

a. mysql\bin> mysqldump --databases clsu > dump.sql

b. mysql\bin> mysqldump -u root --databases clsu > dump.sql

c. mysql\bin> mysqldump clsu > dump.sql

Which one is correct

a. without --databases no create databases and use

b. with --databases no create databases and use

c .without --databases has create databases and use

What is backing up the mysqlhotcopy

a. ARCHIVE

b. MyISAM

c. MyISAM and ARCHIVE

MySQL Tutorials

Tuesday, September 25, 2012

ComSci321 (Database Management System): Introduction in ComSci321-DBMS

Dota 2 Tips and tricks: Dota 2 tips : Gold earning

MySQl Replication Tutorial

Monday, September 24, 2012

MySQL BUCK UP Tutorials