MySQL Developing And Programming Blog

You should add the following lines to your my.cnf file to ensure that if your server has to be rebooted or MySQL restarted, everything will come back online properly: [mysqld] server-id=serverIDmaster-host=masterHostmaster-port=3306master-user=slaveUsermaster-password=slavePasswordmaster-connect-retry=60MySQL Installation and Administration ChecklistCongratulations on completing this chapter. We have covered a myriad of administrative tasks for your new MySQL Server as well as the entire installation process. As always, you should review the bullet points below and make sure you have a firm understanding of each of them. If you feel weak in any areas, don t be afraid to go back and experiment with creating and removing users, databases, and so on. A little practice goes a long way, and it is better to change things now before pushing your server into live usage. You should now be familiar with the following: .Know the differences between a database and flat files. .Understand the limitations and advantages of MySQL. .Know how to read the MySQL documentation. .Download, install, and configure MySQL from source. .Use the various command-line operations provided by the MySQL installation. .Administer databases and users. .Back up and restore databases. .Set up database replication. In the next chapter, we will cover the final piece of required software to bring your LAMP server together: PHP. You ve almost made it through your entire LAMP installation so there s no need to slow down now. Take a deep breath, relax, and you ll be ready to head into Chapter 10, PHP: Installation and Configuration.
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision tomcat hosting services

7.Return to MySQL Client and use this command: SHOW MASTER STATUS; This presents you with the bin log file and offset where the newest record is currently located for your snapshot. 8.Write this information down! You will need it later. The printed results should look similar to the following: +—————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +—————+———-+————–+——————+ | mysql-bin.003 | 73 | test,bar | foo,manual,mysql | +—————+———-+————–+——————+ Here you see the current file is mysql-bin.003, and the offset is 73.1.Now you can unlock the tables by using this command: UNLOCK TABLES; 2.Transfer the /tmp/mysql.snapshot.1.tar file to each of your slave servers /tmp directories. You can choose any manner you wish to do this. 3.Move into your slave servers data directories and untar the file and restart the servers. 4.Log in to the MySQL Client for each slave and perform the following command: CHANGE MASTER TOMASTER_HOST=’masterHostName’, MASTER_USER=’slave_account’, MASTER_PASSWORD=’yes_master’, MASTER_LOG_FILE=’recorded binlog name’, MASTER_LOG_POS=recordedOffset; The recorded binlog name is the name of the bin log file from the master server you received from performing the SHOW MASTER STATUS command. The recorded offset is the offset you received from the same print statement. 5.Run the following to place your slave server in action: START SLAVE; After you have completed this for each slave server, you will have a robust solution scalable to almost any size. If you would like to further customize your replication setup, you should look into the startup directives in Appendix B.
Note: If you are looking for high quality webhost to host and run your jsp application check Vision jsp hosting services

slave server you will need to add a server_id value to the /etc/my.cnf files as well. After you have made these changes, restart each of the servers so that the changes will take effect. 3.Next, you will need to set up an account on the master server that the slave will be using to connect. A special privilege is required for this user, called REPLICATION SLAVE. Because you will be using this account for replication only, you do not need to assign any additional priv- ileges. If you are using more than one slave server, you do not need to create multiple users. In this example, you will be creating a user named slave_account with a password of yes_ master. You should make sure that this slave server can access the master only from the domain it resides in, which will be mydomain.com. To create the account, you will be using the MySQL Client. Log in as the root user and issue the following GRANT command: GRANT REPLICATION SLAVE ON *.* TO ’slave_account’@'mydomain.com’ IDENTIFIED BY ‘yes_master’; NOTERemember that if you are planning on using the LOAD DATA FROM MASTER command on yourslave, you will need to add additional privileges to the account. You should grant the SUPERand RELOAD privileges to this account in addition to SELECT privileges for each table thatwill be replicated. For the purposes of this book, we will be using the tar command to copyour database so we will not be assigning these privileges. 4.Now, with your user created, you will need to lock the current state of your database. Per- form the following: FLUSH TABLES WITH READ LOCK; This prevents any changes from taking effect while you tar the database and transfer it to your slave server(s). WARNINGIf you exit the MySQL Client in this terminal (or terminal window) it may cause MySQL toremove the read lock. It is recommended that you open a new terminal and stay connectedthe MySQL Client. 5.With the database locked, open a new terminal window or switch to a different terminal and move into the /usr/local/mysql/var directory. 6.Perform the following: tar -cvf /tmp/mysql.snapshot.1.tar . This backs up your database to the tmp directory. If you wish to use only a specific database, then use the tar command to tar only that database. You might also wish to exclude the mysql database from your tar so that the user accounts and privileges will not be transferred over.
Note: If you are looking for high quality webhost to host and run your jsp application check Vision jsp hosting services

Queuing master event to the relay logThe I/O thread has received a new event and is currently writing it to the relay log. Waiting to reconnect after a failed master event readAn error has occurred in the connection, and the thread is waiting for the appropriate timeout to occur so that it can attempt to reconnect to the master. Reconnecting after a failed master event readThe thread is currently attempting to reconnect. Waiting for the slave SQL thread to free enough relay log spaceThis means that the SQL thread is currently backed up with work and that the relay log size has reached its max- imum. This shows only if you have specifically set the relay log space limit. Waiting for slave mutex on exitThis is a brief state denoting the thread is stopping. The last thread, the SQL thread, has the least amount of states because of its single purpose of processing events from the relay log. Listed here are its states: Reading an event from the relay logThe thread is reading an event from the relay log for processing. Has read all relay log; waiting for the slave I/O thread to update itAll events from the relay log have been processed. This is the SQL thread s idle state. Waiting for slave mutex on exitThis is a brief state denoting the thread is stopping. Introduction to Replication ApplicationWith an understanding of how MySQL database replication works and an insight into the responsibilities of each applicable thread, you can now take a look at the process for creating a replication setup. This is not the only way to set up replication; there are many techniques. It will, however, give you a straightforward method that works. 1.First, you must have valid installations of MySQL on each server for your replication pro- cess. We will be using two servers, but you can use as many as you wish, performing the actions on each slave server when we perform them on our single slave server. 2.Next you will need to make a few changes to your my.cnf files if they do not already exist. On your master server you should have /etc/my.cnf, including the following two lines: [mysqld] log-binserver_id=1This will enable the bin logs as we discussed earlier as well as provide this server with a unique ID. Remember that each server must have a unique ID and be an integer from 1 to 232. On
Note: If you are looking for high quality webhost to host and run your jsp application check Vision jsp hosting services

Host: db: NULLCommand: Connect Time: 11 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULLIn this example, thread 10 is your I/O thread, and thread 11 is your SQL thread. These states, however, can change depending on what action they are performing. Your master server s BinLog Dump thread can have the following states: Sending binlog event to slaveThis means that the master has received a statement that has updated its binlog and it is currently sending this information to the slave. Finished reading one binlog; switching to the next binlogThis means that the master server has finished reading a binlog and is opening the next to send to the slave. Has sent all binlog to slave; waiting for binlog to be updatedQuite verbose, this means that the slave I/O thread has read all the statements sent by the master. Waiting to finalize terminationYou will most likely not see this state appear because it occurs for only the brief moment the thread is stopping. Each thread has its own thread states that will be given. The slave I/O thread can have the following states: Connecting to masterThe thread is attempting to connect to the master. Checking master versionThis status is displayed for a brief moment after a connection with the master has been established. Registering slave on masterThis status is displayed for a brief moment after a connec- tion with the master has been established. Requesting binlog dumpThis status is displayed for a brief moment after a connection with the master has been established. This is the request to the master for the contents of its bin log. Waiting to reconnect after a failed binlog dump requestThis state is displayed dur- ing the time the thread is asleep, while it is waiting for the timeout to connect again. Reconnecting after a failed binlog dump requestThe slave I/O thread is attempting to reconnect to the master. Waiting for master to send eventThis is the idle status of the thread. When the I/O thread has read all the changes to the database and the master is not currently updating and sending new events, the I/O thread will read this state.
Note: If you are looking for best hosting provider to host and run your tomcat application check Astra tomcat hosting services

When setting up your slave servers, you must have an exact copy of each database to be rep- licated in place on each slave server. This way, when the binary log makes a change, each data- base on each slave server will be an exact duplicate. To do this, you can use the LOAD DATA FROM MASTER command. This command uses a global lock on the master server to ensure that all data is replicated at that point in time. This works only for MyISAM tables, however, and you will need to specify additional privileges your slave account on the master. After you use this com- mand, the slave transfers all data from the master. Then, after the slave is started, it establishes a connection and waits for any additional data that is written to the binary log file. If the server for some reason loses its connection, it will attempt to reestablish that connection every 60 sec- onds. You can change this by using the –master-connect-retry directive. NOTEThe master is never aware of how many slave servers are present. Understanding How Replication Threads WorkBefore you begin issuing commands to set up your master and slave servers, let s take a closer look at how replication works in regard to threads. On the master server, there will be one thread for each slave. Each of these threads is referred to as the BinLog Dump thread and it is in charge of sending each new statement that updates data to a slave. Each slave server will run two additional threads: I/O and SQL. The I/O thread is the thread that connects to the master server and receives the commands from the BinLog Dump thread. These commands are then copied to local files for processing by the SQL thread. These files are called relay logs. Using these two separate slave threads enables the slave server to separate the reading and execution of the statements. Therefore, the reading of the statements from the master is not slowed down by SQL s ability to process these statements. This allows for a type of queue. To view these threads later, after your installation is complete, you can use the SHOW PROCESSLIST command. To make this a bit easier to read, try substituting the semicolon normally used to end a command with G. This will give you a vertical print for better human readability. Per- forming this command on a slave server will give you an output similar to the following: ************************* 1. row ************************* Id: 10 User: system user Host: db: NULLCommand: Connect Time: 11 State: Waiting for master to send event Info: NULL************************* 2. row ************************* Id: 11 User: system user4337Book.
Note: If you are looking for best hosting provider to host and run your tomcat application check Astra tomcat hosting services

your database is nearly as simple, and you will be happy to know that you can recover to an even more recent state then your last backup. However you must have the –log-bin option enabled when running the server. To do so, simply place the directive in your /etc/ my.cnf file. All changes made to the database will then be saved in hostname-bin.xxx files. To perform a full recovery, if you have dumped each database individually, first locate your most recent mysqldump files and perform the following command for each database that needs to be recovered: mysql -uuser -p database < filename.sqlThis will then return your database to the state it was during the last backup. To bring your database, further up-to-date you can then use the following command to execute all binary (bin), logs on your database: mysqlbinlog hostname-bin.[0-9]* | mysql -uuser -pEnter your password when prompted, and all queries will then be run on your database. NOTEThe preceding statement will execute all bin logs on your database. For practical applica- tion, you will want to locate the file that has the closest date to your backup and use thebin logs starting from that point. Performance and ReplicationPerformance and replication can be a complicated issue. With thousands, if not millions, of pos- sible setups, each installation is unique. Truly tuning a server to reach maximum performance requires an understanding of the entire system. This type of understanding reaches a level that is beyond the scope of this book. (See Appendix C, Getting Support, for a list of books and other resources.) For now, we are going to cover some of the more simple procedures and meth- ods of using multiple database servers through replication via a master/slave approach. Replication enables you to run multiple database servers that share the same information. For large-scale projects, this becomes necessary in order to keep queries down to acceptable speeds. If your single database server is being backlogged with requests, and you have made every effort to optimize your queries by adding additional indexes and perhaps additional reference tables for quicker lookups, it is time to consider using replication to enhance your application. Replication is accomplished by MySQL by having the master server keep track of all changes. Therefore, it is necessary to have binary logging enabled for your master server. Each slave server then receives these logging statements from the master server so that it can process the same commands on itself. Remember that the binary log stores only queries and statements that have actually modified data. This prevents a slave server from running needless queries taking up valuable resources.
Note: If you are looking for best hosting provider to host and run your tomcat application check Astra tomcat hosting services

You are now ready to create new users at will for your MySQL Server. After creating a few test users, though, you might wish to delete them. To do so, you use the REVOKE command in addition to the DROP USER statement. These need to be performed in order to ensure that the user is completely eradicated. First you will need to REVOKE all privileges associated with a user: REVOKE ALL PRIVILEGES ON *.* FROM ‘eric’@'localhost’; REVOKE GRANT OPTION ON *.* FROM ‘eric’@'localhost’; This user will no longer have any privileges on the system including the GRANT ability. After this is complete, you will need to use the following: DELETE FROM mysql.user WHERE User=’eric’; FLUSH PRIVILEGES; This removes all users named eric from any host. After you have completed the deletion, flush the privileges to force MySQL to reload all privileges. With that done, you are now ready to begin administering the users of your MySQL Server. Backing Up and Restoring DatabasesThe easiest and most efficient way to perform a backup of a database or all databases is by using the mysqldump command we covered earlier in this chapter. We talked about how you can spec- ify –all-databases or any number of databases and tables. For the purpose of this chapter, we will be running a backup of all databases. You might wish to perform a backup of some databases more often then others. It is now up to you, the system administrator, to judge how important your data is and determine how often you should back up that data. You can choose to have a script run by the cron daemon if you wish or you can choose to back up the server manually. We of course recommend using an automated script because the human memory is anything but infallible. The command you should be using within your script to back up all databases should read like the following: mysqldump -uuser -ppassword –all-databases > /path/to/backup/dir/filename.sqlThis will ensure that all of your databases are then backed up to the location you specify. Additionally, the backup script you have already created, if following along with the book, will backup these backup files to your remote server for safe keeping. You might wish, however, to dump each database individually rather than all together. This makes it easier to rebuild your database(s) should anything go wrong. You can use a command similar to the following: mysqldump –flush-logs –quick –add-drop-table –extended-insert –add-locks -uusername -ppassword database > /path/to/backup/dir/filename.sqlNOTEYou might also wish to create a separate user who has only the SELECT and LOCK TABLESprivileges just for the purposes of backing up your databases.

Hint: If you are looking for high quality and reliable webspace provider to host and run your jsp hosting application check Virtualwebstudio jsp web hosting provider

following is the correct syntax for a user named eric with a password of inst1234 who is allowed to access the system from localhost only: GRANT ALL PRIVILEGES ON *.* TO ‘eric’@'localhost’ IDENTIFIED BY ‘inst1234′ WITH GRANT OPTION; Using the GRANT command does not require that you use that PASSWORD() function because it is already taken care of for you. You will not need to use the FLUSH PRIVILEGES command either, as you would normally need to if you were to use a direct INSERT statement. The syntax of the preceding GRANT statement is to use GRANT and then pass in the privileges. Next you specify ON, followed by which databases and tables you are granting privileges to. Then you use TO, followed by the username @ hostname. You can choose to leave off this option because most users will not need to grant privileges to other users. Here is another example of creating a new user. This is much like the previous example but instead provides access only to all tables on one specific database and does not enable the user to grant privileges: GRANT ALL PRIVILEGES ON test.* TO ‘eric’@'localhost’ IDENTIFIED BY ‘inst1234′; This example is easy enough to follow; we simply changed the wildcard where the database name was to an actual database and left off the WITH statement. You could further narrow their restrictions by replacing the wildcard located after the database name with a table name. This would only allow the given user access to that single table. Now let s take a look at another way to create an account. This example creates an account with no access but to use the MySQL Server, and this user doesn t require a password to connect: GRANT USAGE ON *.* TO ‘eric’@'localhost’; That s all there is to it very simple. Next you ll be looking into a more practical application. You do not want all users to have all privileges or no privileges for your database server. More than likely you will want them to be able to administer the databases they have access to, but no more. To do so, you can replace the ALL PRIVILEGES or USAGE portion with a comma-separated list of the privileges you wish grant. The following is a typical setup for the eric user identified by a password of inst1234 who can use the server from any subdomain of mydomain.com. Notice you will need to use a wildcard here, and in MySQL the wildcard symbol for this instance is a percent symbol (%). The eric user will have access only to the freak database: GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON freak.* TO ‘eric’@'localhost’ IDENTIFIED BY ‘inst1234′; Easy enough.

Hint: If you are looking for high quality and reliable webspace provider to host and run your jsp hosting application check Virtualwebstudio jsp web hosting provider

Here you can see your first table had been correctly inserted into the database. Now take a look at the details of the table by using a new command, DESCRIBE: DESCRIBE food; You should then be given a result as follows: +—————–+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +—————–+————-+——+—–+———+——-+ | food_id | int(11) | YES | | NULL | | | food_name | varchar(30) | YES | | NULL | | | expiration_date | date | YES | | NULL | | +—————–+————-+——+—–+———+——-+ 3 rows in set (0.01 sec) Here, formatted nicely with ASCII characters, you can see each column name listed along with its type, null, key, default, and extra values. These values call all be changed through other commands and during the table creation. Because of the complex nature of the many possibil- ities of MySQL, we will not be covering this in depth. Now that you have your table, drop the table: DROP TABLE food; You can follow it up with this command: DROP DATABASE test; You will once again have a clean installation with only your mysql database installed. With these exercises completed, you will be ready to move on to administering users within MySQL. Creating and Administering UsersIf you are logged in to the MySQL Client, go ahead and quit so you can reenter by using a database from the command line. Because all privileges and user accounts are modified through the mysql database, use the following command to launch the client: /usr/local/mysql/bin/mysql -u root mysql -pAfter you have entered your password, you will once again be brought into the database, but this time you will receive a slightly different message letting you know that MySQL has read the table information into memory for quicker queries. In this section, we will show you several GRANT statements that allow for varying levels of access to be assigned to a user. The first one we will show you is the way to create a super user, someone who has access to all commands for all databases and tables as well as the ability to cre- ate new users.

Hint: If you are looking for high quality and reliable webspace provider to host and run your jsp hosting application check Virtualwebstudio jsp web hosting provider