MYSQL
MySQL’s query cache vs memcached
I did some research about MySQL’s query cache. Some people said it will speed up page load times by 5-15%. Query cache is used to cache SELECT results and later return them without actual executing the same query once again. Having the query cache enabled may result in significant speed improvements, But if tables change […]
check mysql tem files and tables
Try to execute show processlist;, maybe there are some long lasting threads that were not killed for some reason. Similarly execute SHOW SESSION STATUS LIKE ‘Created%’; to check if mysql hasn’t created to many temporary tables. Server restart automatically closes all open temp tables and kills threads, so the application might run quicker.
dump and import mysql database avoid error codes
Do not do this, since it might screw up encoding:
|
1 |
mysqldump -uroot -p database > utf8.dump # this is bad |
Better do:
|
1 |
mysqldump -uroot -p database -r utf8.dump |
Note that when your MySQL server is not set to UTF-8 you need to do mysqldump –default-character-set=latin (!) to get a correctly encoded dump. If you only want to dump the structure without data, use
|
1 |
mysqldump -uroot -p --no-data database -r utf8.dump |
Importing a dump safely Do […]
php very useful mysql connect function for small project
This class is very useful for small projects, it easy and fast to connect mysql with php script. Thank you Ciprian Mocanu to provide this document. The Class:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 |
<?php class mysql { var $con; function __construct($db=array()) { $default = array( 'host' => 'localhost', 'user' => 'root', 'pass' => '', 'db' => 'test' ); $db = array_merge($default,$db); $this->con=mysql_connect($db['host'],$db['user'],$db['pass'],true) or die ('Error connecting to MySQL'); mysql_select_db($db['db'],$this->con) or die('Database '.$db['db'].' does not exist!'); } function __destruct() { mysql_close($this->con); } function query($s='',$rows=false,$organize=true) { if (!$q=mysql_query($s,$this->con)) return false; if ($rows!==false) $rows = intval($rows); $rez=array(); $count=0; $type = $organize ? MYSQL_NUM : MYSQL_ASSOC; while (($rows===false || $count<$rows) && $line=mysql_fetch_array($q,$type)) { if ($organize) { foreach ($line as $field_id => $value) { $table = mysql_field_table($q, $field_id); if ($table==='') $table=0; $field = mysql_field_name($q,$field_id); $rez[$count][$table][$field]=$value; } } else { $rez[$count] = $line; } ++$count; } if (!mysql_free_result($q)) return false; return $rez; } function execute($s='') { if (mysql_query($s,$this->con)) return true; return false; } function select($options) { $default = array ( 'table' => '', 'fields' => '*', 'condition' => '1', 'order' => '1', 'limit' => 50 ); $options = array_merge($default,$options); $sql = "SELECT {$options['fields']} FROM {$options['table']} WHERE {$options['condition']} ORDER BY {$options['order']} LIMIT {$options['limit']}"; return $this->query($sql); } function row($options) { $default = array ( 'table' => '', 'fields' => '*', 'condition' => '1', 'order' => '1' ); $options = array_merge($default,$options); $sql = "SELECT {$options['fields']} FROM {$options['table']} WHERE {$options['condition']} ORDER BY {$options['order']}"; $result = $this->query($sql,1,false); if (empty($result[0])) return false; return $result[0]; } function get($table=null,$field=null,$conditions='1') { if ($table===null || $field===null) return false; $result=$this->row(array( 'table' => $table, 'condition' => $conditions, 'fields' => $field )); if (empty($result[$field])) return false; return $result[$field]; } function update($table=null,$array_of_values=array(),$conditions='FALSE') { if ($table===null || empty($array_of_values)) return false; $what_to_set = array(); foreach ($array_of_values as $field => $value) { if (is_array($value) && !empty($value[0])) $what_to_set[]="`$field`='{$value[0]}'"; else $what_to_set []= "`$field`='".mysql_real_escape_string($value,$this->con)."'"; } $what_to_set_string = implode(',',$what_to_set); return $this->execute("UPDATE $table SET $what_to_set_string WHERE $conditions"); } function insert($table=null,$array_of_values=array()) { if ($table===null || empty($array_of_values) || !is_array($array_of_values)) return false; $fields=array(); $values=array(); foreach ($array_of_values as $id => $value) { $fields[]=$id; if (is_array($value) && !empty($value[0])) $values[]=$value[0]; else $values[]="'".mysql_real_escape_string($value,$this->con)."'"; } $s = "INSERT INTO $table (".implode(',',$fields).') VALUES ('.implode(',',$values).')'; if (mysql_query($s,$this->con)) return mysql_insert_id($this->con); return false; } function delete($table=null,$conditions='FALSE') { if ($table===null) return false; return $this->execute("DELETE FROM $table WHERE $conditions"); } } |
Example usage:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
$connection_information = array( 'host' => 'localhost', 'user' => 'root', 'pass' => '', 'db' => 'test' ); $m = new mysql($connection_information); //simple and complex query (I recommend you use the select method of the class rather than this) $result = $m->query('SELECT * FROM `users`'); var_dump($result); //this will output an array like this: $array[$count][$table][$field] //simple execute command (I recommend using the delete and the insert and the update methods of the class) $result = $m->execute('DELETE FROM `pages` WHERE `id`=5'); var_dump($result); //returns true if ok and false if not //method to select (clean and beautiful) $result = $m->select(array( 'table' => 'users', 'condition' => 'active=1 AND type=1' )); var_dump($result); // Will output something like this: /* array (3) { [0] => array (1) { ['users'] => array (4) { ['id'] => int (5), ['name'] => string (7) "ciprian", ['active'] => int (1), ['type'] => int (1), ['email'] => string (14) "ciprian@mbe.ro" } }, [1] => ... } */ //you can also get only one row (and a simple array like this $array[$table_field]) $result = $m->row(array( 'table' => 'users', 'condition' => 'active=1 AND type=1' )); var_dump($m); //returns only the first row in an array arranged like this $array[$table_field] //or you can get only a field (for example you need the name of the user with id = 5) $name=$m->get('users','name','id=5'); var_dump($name); // Will output something like this: /* string (7) "ciprian" */ //you can also insert into the table $data = array( 'name' => 'ion', 'active' => 0, 'email' => 'ion@mbe.ro' ); $result = $m->insert('users',$data); var_dump($result); //returns true if ok and false if not //update the table (let's assume we have a pages table and we need to set the views of the page with + 1 $data = array( 'views' => array( 'views+1' ) ); $result = $m->update('pages',$data,'id='.$current_page_id); var_dump($result); //returns true if ok and false if not //PS: Notice I put the value of the views in another array. You can do that in the update as well as in the insert. If you put it like that, no mysql_real_escape_string will be called for that value when updating / inserting. //deletion is also possible $result = $m->delete('pages','id=5'); //deletes page with id 5 var_dump($result); //returns true if ok and false if not |
mysql production server and development sync
UPDATE! Code moved to bitbucket: https://bitbucket.org/markoshust/syncdb/src Often times, you need to pull down an up-to-date version of Magento from your production server to your development or staging servers. This involves: Executing a mysqldump on production. Zipping it up. Secure copying the zipped file from production to development (or staging). Unzipping the file locally. Updating the […]
Memory Game
Game link: http://www.maning.ca/ats/memory-game/ Download Demo: http://www.maning.ca/ats/memory-game/memory-game-Demo.zip “Memory game is a card game in which all of the cards are laid face down on a surface and two cards are flipped face up over each turn. The object of the game is to turn over pairs of matching cards.” I used half day to program this […]
Choosing innodb_buffer_pool_size
Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I’ve seen a lot of clients which came through extreme sufferings leaving it at default value (8M). So if you have dedicated MySQL Box and you’re only using Innodb tables you will want to give all […]
Move a whole lamp site to another server
1. tar all folder
|
1 |
tar cvfz filename.tar.gz folder |
2. backup mysql DB
|
1 |
$ mysqldump -u root -p dbname >~/db_name.sql |
3.scp files from ssh to another server
|
1 |
scp -r filename username@127.0.0.1:/path/www.googel.com |
4. restore the DB
|
1 2 3 4 5 6 7 8 |
$ mysql -u root -pmypassword Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 812 Server version: 5.1.20-beta-log FreeBSD port: mysql-server-5.1.20 Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database dbname_copy mysql> use dbname_copy mysql> source ~/db_name.sql |
5. tar gz file
|
1 |
tar -zxvf trio.myracksales.com.tar.gz |
6. Update httpd.conf 7. chown -Rf permissions 8. update DNS nameserver or ip
How to copy a mysql database using mysqldump
1. Back-up the original database that you want to “clone” In case you you have a mysql password for user root you need to use mysql(dump) -u root -p dbname and you will be asked for root password by mysql program. If you don’t wish to be asked you can youse mysql(dump) -u root -ppassword […]
MYSQL5 event scheduler example
MySQL 5.1 Event Scheduler, Conceptually, this is similar to the idea of the UNIX crontab (also known as a “cron job”) or the Windows Task Scheduler. What is “Event Scheduler” event is an object whose statements are executed in response to the passage of a specified time interval. An event performs a specific action according […]