OSS Robot

If you have a script that you want to setup at varying time intervals, including but not limited to certain days of the week or specific hours in a day then those commands should go in /etc/crontab.

However, if you want to simply run your script on an hourly or daily basis you can simply put a sym link in /etc/cron.daily or hourly.

Best way to do this is put your actual script in /usr/bin and create a sym link in the appropriate cron directory.

/etc/cron.daily$ sudo ln -s /usr/bin/memcache_save_data.py ./memcache_save_data

Bingo! No more to do. /etc/crontab runs all files in this folders automatically

Note: Cron does not allow you to have file extensions on files in /etc/cron dirs. Also, if you need to pass parameters to the script then you need to run this in /etc/crontab as there is no way to specify these with the above option.

Uncategorized

If you are unable to get the proper permissions/access on your system because of a shared hosting environment then you may just want to dump your local database and send it to a remote server. This is a simple way to setup a daily copy.

1. Create user called ‘remote’ on the slave (destination for backup) that you can access from the master (the data that needs to be backed up)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to remote@'MASTERIPADDRESS' IDENTIFIED BY 'PASSHERE';
Query OK, 0 rows affected (0.07 sec)

mysql> flush privileges;

Note:
*REPLICATION CLIENT Gives the right to the user to ask where the slaves/masters are.
*REPLICATION SLAVE Needed for the replication slaves (to read binlogs from master).

IMPORTANT! - Make sure /etc/mysql/my.cnf bind-address is not 127.0.0.1 - if so comment. If change, you will need to restart mysql.

2. Verify you can connect from the master to the slave.

# mysql -h ipaddresstoslave -u remote -p

3. Create a script to run the commands.

#!/usr/bin/python
import os
import datetime
file='mysql-dump-all-%s.sql' % (datetime.date.today())
print "Creating backup file..."
os.system('mysqldump --all-databases -u jester_root -pREMOTEPASS > ~/'+file)
print "Dumping to titan..."
os.system('mysql -h IPTOSLAVE -u remote -pREMOTEPASS < ~/'+file)
print "Deleting local sql file..."
os.system('rm ~/'+file)


4. Add file to cron

Done!

Uncategorized

What is replication and what should it is and what it isnt…

Master-Master - synchronous replication where each server updates one another.

Pros: Used for hot standby situation. Write to one master only to avoid primary key collisions.
Cons: Network intensive.

Master-Slave - Primary used to share the load. Writes go to master from client,reads go to slave.

Pros: Asyncronous updates (one way) Simple, inexpensive. Hot cache from slave.
Cons: Not good for heavy write loads.

source: http://www.mysqlperformanceblog.com/2009/11/13/finding-your-mysql-high-availability-solution-%E2%80%93-replication/

http://dev.mysql.com/doc/refman/5.1/en/replication.html

How to setup mysql replication

Create new replication user on master.

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to slave@'SLAVEIPADDRESS' IDENTIFIED BY 'PASSHERE';
Query OK, 0 rows affected (0.07 sec)


IMPORTANT!!! - make sure /etc/mysql/my.cnf 127.0.0.1 for bind-address is commented out or cannot connect from the slave!


#bind-address = 127.0.0.1


Now verify can connect from slave:


root@slave:/var/log# mysql -h MASTERIP -P 3306 -u slave -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.1.37-1ubuntu5-log (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>


Configure master.

edit /etc/my.cnf

Add the following.

[mysqld]
…
log-bin=mysql-bin
server-id=1 #master id
…


Make backup of Master.


root@donkey [~]# mysqldump –all-databases -u root -p > mysql-dump-master.sql
Enter password:


Write down the position of the master status for later…

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


Shudown mysql


root@donkey [~]# sudo /etc/init.d/mysql stop Shutting down MySQL.

Transfter file to Slave.

scp mysql-dump-all-10-15-09.sql root@slave:~

Import Data

root@titan:~# mysql -u root -p < ~/mysql-dump-all-10-15-09.sql
Enter password

Slave /etc/mysql/my.cnf

[mysqld]
server-id = 2
log_bin = mysql-bin.log


Now set master from slave.

mysql> CHANGE MASTER TO MASTER_HOST='205.234.235.89',MASTER_USER='replication_user', MASTER_PASSWORD='PASSHERE',MASTER_LOG_POS=106,MASTER_LOG_FILE='mysql-bin.000001';
Query OK, 0 rows affected (0.00 sec)

*log file and position should be the output from master ’show master status’; command.
*in need to reset the above then do ’stop slave’, ‘reset slave’ and issue the CHANGE MASTER TO cmd again.

mysql> start slave
-> ;
Query OK, 0 rows affected (0.00 sec)

Verify Master/Slave are replicating. ‘Slave IO Running’ and ‘Slave SQL Running’ should both be set to “YES”

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.139.68.174
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 43981
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 33858
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 43981
Relay_Log_Space: 33858
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Check master to see if positon of slave ans master are in sync!

Done!

Uncategorized

#Grant options: ALL, SELECT, UPDATE, INSERT
mysql> GRANT SELECT ON *.* to 'someuser'@'IPCONNECTINGFROM or %' IDENTIFIED BY 'passwordhere';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Verify setup…

mysql> select host, password, user from user;

Uncategorized