Denie's Tech Blog

This script will output the queries need to change all fields/tables to a different collation.

It is HIGHLY suggested you take a MySQL dump prior to running any of the generated

This code is provided as is and without any warranty

<?php

// this script will output the queries need to change all fields/tables to a different collation

// it is HIGHLY suggested you take a MySQL dump prior to running any of the generated

// this code is provided as is and without any warranty

die(”Make a backup of your MySQL database then remove this line”);

set_time_limit(0);

// collation you want to change:

$convert_from = ‘latin1_swedish_ci’;

// collation you want to change it to:

$convert_to   = ‘utf8_general_ci’;

// character set of new collation:

$character_set= ‘utf8′;

$show_alter_table = true;

$show_alter_field = true;

// DB login information

$username = ‘username’;

$password = ‘password’;

$database = ‘database’;

$host     = ‘localhost’;

mysql_connect($host, $username, $password);

mysql_select_db($database);

$rs_tables = mysql_query(” SHOW TABLES “) or die(mysql_error());

print ‘<pre>’;

while ($row_tables = mysql_fetch_row($rs_tables)) {

$table = mysql_real_escape_string($row_tables[0]);

// Alter table collation

// ALTER TABLE `account` DEFAULT CHARACTER SET utf8

if ($show_alter_table) {

echo(”ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\r\n”);

}

$rs = mysql_query(” SHOW FULL FIELDS FROM `$table` “) or die(mysql_error());

while ($row=mysql_fetch_assoc($rs)) {

if ($row['Collation']!=$convert_from)

continue;

// Is the field allowed to be null?

if ($row['Null']==’YES’) {

$nullable = ‘ NULL ‘;

} else {

$nullable = ‘ NOT NULL’;

}

// Does the field default to null, a string, or nothing?

if ($row['Default']==’NULL’) {

$default = ” DEFAULT NULL”;

} else if ($row['Default']!=”) {

$default = ” DEFAULT ‘”.mysql_real_escape_string($row['Default']).”‘”;

} else {

$default = ”;

}

// Alter field collation:

// ALTER TABLE `account` CHANGE `email` `email` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL

if ($show_alter_field) {

$field = mysql_real_escape_string($row['Field']);

echo “ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default; \r\n”;

}

}

}

?>

0) Backup your database.
You should probably be doing this already.  Now’s a good time to make sure that your backups ran.

1) Create the script.
You’ll need the correct permissions to query the database. Here’s the command.  Be sure to change <DATABASE_NAME> as it fits.

# mysql -p -e "show tables in <DATABASE_NAME>;" | \
tail --lines=+2 | \
xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql

2) Run the script.

# mysql --database=<DATABASE_NAME> -p < alter_table.sql

3) Verify it by running this command in mysql:

mysql> show table status;

Read more …

/usr/local/bin/mysql.backup.sh

#!/bin/bash
NOW=$(date +"%m-%d-%Y")
OLD=$(date +"%m-%d-%Y" --date="3 days ago")
PROJECT="project_name"
LOCATION="/home/backup"
FILE="$PROJECT.$NOW.sql"
FILE2="$FILE.gz"
FILEOLD="$PROJECT.$OLD.sql.gz"
EMAIL="youremail@domain.com"
$SQLUSER="username"
$SQLPASS="password"
$SQLNAME="database_name"

cd $LOCATION ; \
rm -f $FILEOLD ; \
mysqldump -u $SQLUSER --password=$SQLPASS $SQLNAME > \
$LOCATION/$FILE ; \
gzip $LOCATION/$FILE ; \
echo "Backup location is in $LOCATION/$FILE2" | \
mail -s "[$PROJECT] MySQL Backup" $EMAIL

Then you can put it on your cron (background process)

Below cron will execute the script on Saturday at 12AM:

0 0 * * 6 /usr/local/bin/mysql.backup.sh

Good luck!

By default, Apache comes preconfigured to serve a maximum of 256 clients simultaneously. This particular configuration setting can be found in the file /etc/httpd/conf/httpd.conf

If your server has 2 GB of RAM, and you’re sharing your server with MySQL(true in my case), you’ll want to reserve about half of it for Apache (1 GB)

Read more …

To setup Master-Slave Replication the first thing you need to do is create a user on the Master server that allows replication.

Read more …