mysql basic replication setup
mysql replication is fairly common and the benefits go without saying. Below is what I have found to be the best process for configuring replication on two vanilla mysql devices.
Install mysql on both nodes.
yum install mysql-server -y
On both nodes confirm that mysql is started and listening for remote connection, not bound to loopback.
/etc/init.d/mysqld start chkconfig mysqld on netstat -ntlp | grep mysqld
reset mysql root password
If you have forgotten the root mysql password and you do not have an alternate user with equivalent permissions you can easily define a new root password following the below steps.
Note: Obviously mysql connections will be interrupted during this processes as we will be restarting the service and not listening on the network for a bit.
/etc/init.d/mysqld stop
mysqld_safe --skip-networking --skip-grant-tables
mysql -u root
mysql> use mysql;
mysql> update user set Password=PASSWORD('testpassword') where User='root';
mysql> quit
/etc/init.d/mysqld restart
add mysql user
How to add a mysql user, define permissions, and set password in one command.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';
I primarily use the first method, however, obviously different circumstances (permissions) call for different commands.
After you make any permission or user changes be sure to run 'flush privileges;' to apply your changes. (Note: when you restart mysql new changes are applied at this time as well).
Link where information was taken from: http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
simple mysql health check
Simple? Yes. Useful? Yes.
Although the below is very simple it can be a very useful and effective way to regularly check apache functionality and its ability to communicate with mysql.
<html>
<head>
<title>Test mysql communications</title>
</head>
<body>
<!--test-->
<?php
$dbhost = 'localhost:3306';
$dbuser = 'trunty';
$dbpass = 'password';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn)
{
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($conn);
?>
</body>
</html>