mysql code

Restore a single table from a large MySQL backup

Say, for some reason, you need to restore the entire contents of a single table from a HUGE mysqldump generated backup containing several tables. For example:

   1  
   2  create table `baz`;
   3  
   4  GIGS OF SQL YOU DON'T WANT;
   5  
   6  create table `foo`;
   7  
   8  A COUPLE THOUSAND LINES YOU DO WANT;
   9  
  10  create table `bar`;
  11  
  12  MORE SQL YOU DON'T WANT;

With a little dash 'o ruby, you can extract just the part you want:

   1  
   2  $ ruby -ne '@found=true if $_ =~ /^CREATE TABLE `foo`/i; next unless @found; exit if $_ =~ /^CREATE TABLE (?!`foo`)/i; puts $_;' giant_sql_dump.sql > foo.sql
   3  $ cat foo.sql
   4  create table `foo`;
   5  
   6  A COUPLE THOUSAND LINES YOU DO WANT;
   7

You can then easily restore that entire table:

   1  
   2  $ mysql mydatabase -e 'drop table foo'
   3  $ mysql mydatabase < foo.sql

to ruby mysql backup mysqldump restore by jnewland on Nov 28, 2007

Some problems with charset in UTF-8 ?

So you can use this request MySQL before all others, for fix your problems :

   1  
   2  ...
   3  mysql_query( "SET NAMES 'utf8' " );
   4  ...

Source: ab-d.fr
Languages: PHP and MySQL

to sql mysql php encoding utf-8 unicode by ki4ngel on Nov 23, 2007

Dump mysql data into a local test file

// SQL to dump MySql data into a local cvs file

   1  
   2  SELECT * INTO OUTFILE '/tmp/file.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM <tablename>

to mysql dump by whomwah on Nov 20, 2007

Use get_magic_quotes_gpc();

   1  
   2  function f_magic_quotes($text) {
   3  	if ( !get_magic_quotes_gpc() ) {
   4  		return addslashes($text);
   5  	} else {
   6  		return $text;
   7  	}
   8  }

   1  
   2  function f_clean_quotes($text) {
   3  	if ( !get_magic_quotes_gpc() ) {
   4  		return $text;
   5  	} else {
   6  		return stripslashes($text);
   7  	}
   8  }

Source: ab-d

to sql mysql php text by ki4ngel on Nov 18, 2007

disable SQL / MySQL in rails logging in development mode

// description of your code here

   1  
   2  ActiveRecord::Base.logger = Logger.new("#{RAILS_ROOT}/log/#{RAILS_ENV}_database.log")
   3

goes at end of config/env

to ruby rails sql mysql database log logging by jm3 on Nov 01, 2007

PHP : Filas al azar / Random Rows

Filas al azar / Random Rows

   1  
   2  function dame_unas_filas_al_azar($tabla,$numero_de_filas)
   3  {
   4  $sql="SELECT * FROM ".$tabla." ORDER BY RAND(NOW()) LIMIT ".$numero_de_filas;
   5  $resultado = mysql_query($sql);
   6  	if($resultado)
   7  	{
   8  	return mysql_fetch_array($resultado);
   9  	}
  10  	else
  11  	{
  12  	return false;
  13  	}
  14  }

to sql random mysql php filas azar by Ricardo on Oct 19, 2007

mysql dump to another server

// Using UNIX pipe concept one can dump database to another server securely using ssh protocol.
// All you need remote execution rights for the ‘dd’ command, over SSH.

   1  
   2  mysqldump -u USERnAME -p'PASSWORD' YOUR-DATABASE-NAME | ssh user@remote.server.com "dd of=/mysql/$(date +'%d-%m-%y')"

to mysql by shantanuo on Oct 02, 2007

simple backup PHP application (php files + mysql db)

// backup a folder contains all .php files and it's mysql database. a backup.ini file is required for storing mysql root login and what folder to be backed up.

   1  
   2  #!/bin/sh
   3  
   4  ########################################
   5  # simple sctipt for daily / hourly backup of PHP app + Mysql DB
   6  # Copyleft (c) Sayid Munawar. chenull@yahoo.com
   7  # LICENSE: anyone can copy / modify / distribute. whatever lah
   8  #
   9  # example of workhours backup (8 am - 5 pm. Mon - Fri)
  10  # 0 8-17 * * 1-5 /home/backup/backup.sh
  11  #
  12  # example of simple dayly backup ( 6 pm. Mon - Fri)
  13  # 0 18 * * 1-5 /home/backup/backup.sh
  14  ########################################
  15  
  16  ########################################
  17  # example of backup.ini. REMOVE ALL leading '#'s
  18  # file must be chmod 600
  19  #[main]
  20  #target = /home/backup/storage
  21  #mysql_user = root   ; root can connect to any db
  22  #mysql_pass = secret ; mysql root password
  23  #
  24  #[hukum]
  25  #path = /home/hukum
  26  #mysql_db = hukum
  27  #
  28  #[phpmyadmin]
  29  #path = /home/phpmyadmin
  30  #mysql_db = test
  31  ########################################
  32  
  33  PATH=$PATH:/usr/bin:/bin:/usr/local/bin
  34  
  35  inifile=`dirname $0`/backup.ini
  36  
  37  test ! -r $inifile && echo "ERROR! backup.ini not found nor readable" && exit 1
  38  
  39  #test apakah backup.ini bisa dibaca orang lain
  40  echo -n `stat -c '%a' $inifile` | grep -q '[0-7]00' >/dev/null 2>&1
  41  test $? -gt 0 && echo "ERROR! $inifile can be read by others" && exit 2
  42  #test `stat -c '%U' $inifile` != 'root' && echo "ERROR! $inifile ownernya bukan root" && exit 3
  43  
  44  # function to parse ini file (backup.ini)
  45  #
  46  # $1 -> file.ini
  47  # $2 -> section
  48  _parse_ini () {
  49      if [ -z "$1" ] || [ -z "$2" ]; then return 0; fi
  50      eval `cat $1 | \
  51         sed -e 's/[[:space:]]*\=[[:space:]]*/=/g' \
  52             -e 's/;.*$//' \
  53             -e 's/[[:space:]]*$//' \
  54             -e 's/^[[:space:]]*//' \
  55             -e "s/^\(.*\)=\([^\"']*\)$/\1=\"\2\"/" | \
  56         sed -n -e "/^\[$2\]/,/^\s*\[/{/^[^;].*\=.*/p;}"`
  57  }
  58  
  59  _parse_ini $inifile main
  60  
  61  # coba konek 
  62  mysql -u $mysql_user  -p${mysql_pass} -e '' > /dev/null 2>&1
  63  test $? -gt 0 && echo "ERROR! Failed to connect to mysql" && exit 4
  64  
  65  # bikin target kalo blum ada
  66  test ! -d $target && mkdir -p $target
  67  
  68  hari=`date +%A`
  69  jam=`date +%H`
  70  
  71  # get pwd
  72  cwd=`pwd`
  73  
  74  # looping
  75  for section in `cat $inifile | grep '^\[' | grep -v main | sed 's/\[//' | sed 's/\]//'`; do
  76    outdir=$target/$section/$hari/$jam
  77    echo Backing up ${section} to $outdir...
  78    # parse .ini
  79    _parse_ini $inifile $section
  80    # cek dulu
  81    test ! -d $path && echo "Warning: $path not found.  Backup process of $section skipped" && echo && continue
  82    mysql -u $mysql_user  -p${mysql_pass} -e '' $mysql_db > /dev/null 2>&1
  83    test $? -gt 0 && echo "Warning: Database $mysql_db not found.  Backup process of $section skipped" && echo && continue
  84  
  85    test ! -d $outdir && mkdir -p $outdir
  86    cd $path
  87    tar -czpf $outdir/$section.tar.gz .
  88    cd $cwd
  89    mysqldump -Q -u $mysql_user -p${mysql_pass} $mysql_db | gzip > $outdir/${mysql_db}.sql.gz
  90    ln -fs $outdir/$section.tar.gz $target/$section/latest.tar.gz
  91    ln -fs $outdir/${mysql_db}.sql.gz $target/$section/latest.sql.gz
  92  done

to linux mysql php bash backup ini parse sh by chenull on Sep 13, 2007

Connect to mysql via another client

I needed to connect to MySql database via another client. I first had to add the relevant privileges to the database:

   1  
   2  GRANT ALL PRIVILEGES ON <database>.* TO <user>@'%' IDENTIFIED BY '<password>';

Then open up the port in the firewall via /etc/sysconfig/iptables

   1  
   2  -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

to mysql iptables privileges by whomwah on Aug 29, 2007

unix wizards of the realm:

// SVN ignore based on .cvsignore file:

   1  
   2  svn propset svn:ignore -F .cvsignore .

// grep:

   1  
   2    … with line number: -n
   3    … with file name: -H

// os x housekeeping:

// install perl module:

   1  
   2  sudo perl -MCPAN -e 'install Bundle::LWP'

// meta refresh (i have never typed this line start to finish in my life. i have probably copy-pasted it 7,000 times

   1  
   2  <meta http-equiv=Refresh content="0; URL=http://blog.jm3.net/" />

// get files off codeswami:

   1  
   2  ssh -l cs 208.101.26.91

// SQL tricks:
http://jm3.net/cgi-bin/safe/wiki.pl?MySqlLibrary

to sql unix mysql perl grep svn by jm3 on Aug 19, 2007

Search DZone Snippets

Popular @ dzone.com

Related Tags

Never been to DZone Snippets before?