mysql | SpotGeek.net

O script é bem simples, faz um dump em um arquivo SQL, compacta e apaga o SQL (deixando só o arquivo compactado). Para ficar mais divertido, mandei ele apagar os backups antigos (mantém só os 10 últimos (exagero!)) e mandar e-mails avisando o status do backup. Ele manda e-mails sempre, mas pelo código você vai ver como é simples mandar e-mails só em caso de erros. No início achei interessante mandar e-mails sempre para confirmar que o backup está funcionando legal.

Depois coloquei na cron do Linux (Ubuntu) para executar todos os dias às 2h da manhã (que você pode configurar de acordo com sua necessidade).

Segue abaixo:

#!/bin/bash
U_PASTA="/home/user/backup" # < -- substitua pelo caminho onde vai salvar o backup
U_DATA=$(/bin/date +%Y%m%d%H%M%S)
U_CAMINHO="backup-$U_DATA.sql"
U_CAMINHOTAR="backup-$U_DATA.tar.gz"
U_HOST="localhost"
U_USER="root"
U_PASSWORD="********"
U_DATABASE="minha_base_mysql"
U_EMAIL="meuemail@meudominio.com.br"
#
erro=""
#
cd $U_PASTA
#
#
# Faz o backup do MySQL
#
mysqldump -h $U_HOST -u $U_USER -p$U_PASSWORD $U_DATABASE > $U_CAMINHO
if [ $? -ne 0 ]
then
    erro="Erro na geracao do SQL"
fi
#
#
# Compacta o arquivo
#
if [ "$erro" == "" ]
then
    tar zcvf $U_CAMINHOTAR $U_CAMINHO
    if [ $? -ne 0 ]
    then
        erro="Erro ao compactar o SQL"
    fi
fi
#
#
# Remove o arquivo sql que nao sera mais usado
#
if [ "$erro" == "" ]
then
    rm -f $U_CAMINHO
    if [ $? -ne 0 ]
    then
        erro="Erro ao apagar arquivo SQL temporario"
    fi
fi
#
#
# Apaga arquivos antigos e mantem apenas os 'n' ultimos
#
n=10
c=0
for i in *.tar.gz
do
    let c=$c+1
done
if [ $c -gt $n ]
then
    for i in *.tar.gz
    do
        if [ $c -le $n ]
        then
            break
        fi
        rm $i
        let c=$c-1
    done
fi
#
if [ "$erro" == "" ]
then
    erro="Backup efetuado com sucesso!"
    subject="[Sucesso]"
else
    subject="[Erro]"
fi
echo "$erro => $U_CAMINHOTAR" | mail -s "$subject Backup em $(/bin/date +'%d/%m/%Y %H:%M:%S')" $U_EMAIL
#
# Volta para a pasta anterior
cd -

Para agendar a execução, digite:

$ crontab -e

Vai abrir um editor de texto com o arquivo de configuração da cron. Daí você pode digitar algo como:

# m h  dom mon dow   command
00 02 * * * /home/user/backup_bd.sh

Onde backup_bd.sh é o arquivo do script que passei acima.

É isso. Espero que seja útil.

PS: Modifiquei algumas coisas na hora de publicar para não expor minha base, daí espero que não tenha cometido nenhum erro.

Outro PS: O script é meio amador, mas funciona legal. A base que faço backup gera um arquivo sql de 117Mb, compactado cai para 18Mb, e tudo isso em cerca de 15 segundos…

via: Andre Noel

Abr

Backup MySQL em servidor remoto

Backup entre dois servidores [Local] e [Remote].

mysqldump -u USERNAME -pPASSWORD nome-da-db >/home/mysqlbak/mysqldump.sql
rsync -avz /home/mysqlbak/mysqldump.sql username@ip-do-servidor-remoto:/home/mysqlbak/mysqldump.sql

Podem ler mais neste post: http://www.webkaput.com/backup-em-servidor-remoto/

Abr

Oracle compra Sun

O negócio entre a Sun e a Oracle acaba de ser anunciado e analistas começam a discutir qual será o futuro do MySQL, base de dados.

Como exemplo nada melhor que este comando sql.

BEGIN;
INSERT INTO oracle (SELECT interesting_stuff FROM sun WHERE likely_to_make_money = 1);
DROP sun; COMMIT; http://ginx.com/-50VNH

Mar

mysql admin and development tools

Original article : http://www.smashingmagazine.com/2009/03/25/mysql-admin-and-development-tools-round-up/

The most popular and best open source database is MySQL. Simple as that. It is consistently fast , reliabile and very, very easy to use. One of MySQLs main features is its cross-platform support, it will run on almost all operating systems, including Windows, OSX, Linux and many more. So, MySQL is the best database, and we all use it. The question now is which is the best MySQL Admin and Development Tool?

In the overview below we present some of the most useful and most popular MySQL Admin and Development Tools for Mac, Windows and Linux, for beginners and for professionals. Among other tools we feature phpMyAdmin, MySQL Workbench, HeidiSQL and SequelPro. Please let us know if some useful MySQL-tool is missing in the list by commenting upon this article.

You may be interested in the following related posts:

MySQL Workbench (Win, Linux, Mac)

MySQL Workbench
This application is a fully featured visual database design package that includes database design, modeling, creation and maintenance bundled into a single environment for the MySQL database system. The free version is the Community Edition, which is loaded with features that will meet almost all your requirements. If you feel adventurous you could always go for the commercial version (Standard Edition).

Help and Support
MySQL Workbench Help
Download
Select your download version

HeidiSQL – MySQL made easy (Win, Linux)

Heidi SQL
Heidi SQL is lightweight and fast with a flexible interface, it allows you to browse and edit your databases databases, manage tables, view and edit records, manage user-privileges, import data from text files, run SQL queries, synchronize tables between two databases and export selected tables to other databases or SQL scripts, and all this from an intuitive Windows interface.

Help and Support
Heidi SQL Forums
Download
jHeidi Alpha 4 download

You can also download a small app for the iPhone called jHeidi Mobi, whicht is the only application to offer Oracle and MySQL access in a mobile application. jHeidi Mobi Download Page

Aqua Data Studio (Win, Linux, OS X, Solaris)

Aqua Data Studio
This complete IDE, has four major areas of functionality: Database query and administration tools; a suite of compare tools for databases, source control and file systems; integrated source control client for Subversion (SVN) and CVS; and a powerful database modeler. The Studio has a single consistent interface to all major relational databases, this allows the developer to simultaneously tackle several tasks from within a single application.

Help and Support
Aqua Data Studio Support
Download
Aqua Data Studio Downloads Page

phpMyAdmin

phpMyAdmin
This very popular, feature rich and easy to use free tool, written in PHP (obviusly), has been built to handle the full administration of MySQL. It can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage users and permissions, and manage keys on fields. A nice feature from this legendary tool is that you can download and change the theme (not exactly a monumental feature, but a unique feature), download themes here: phpMyAdmin Themes (remember themes are version specific).

SQLyog MySQL GUI (Win)

SQLyog
SQLyog is another popular SQL management tool that has a couple of useful features such as visual schema designer, visual query builder, intelligent code completion and a data / structure synchronization. It support HTTP / HTTPs / SSH tunneling and SSL connections. You can also specify a job manager, define a scheduled database backup and use a notification service to be reminded when some jobs are finished. There is a free Community version and a commercial Enterprise edition. You may also use MONyog, a MySQL Monitor and Advisor.

Help and Support
SQLyog Forum
FAQ
SQLyog FAQ
Download
SQLyog Downloads Page

MySQL-Front (Win)

MYSQL Front
This graphical GUI for the MySQL database, is a “real” application, it can offer a more refined user-interface than is possible with systems built on PHP and HTML. Response is immediate, as there is no delay of reloading HTML-pages. If your provider allows it, MySQL-Front can make direct contact with the database. Otherwise, only one small script needs to be installed on the publishing website. Login information are stored on your hard disk, so you no longer have to log on to different web interfaces.

Help and Support
MYSQL Front Forum
Download
MYSQL Front Downloads Page

Sequel Pro (Mac OS X)

Sequel Pro
This MAC OSX database management app gives you direct access to your MySQL databases on local and remote servers with support for importing and exporting data from popular files including SQL, CSV and XML. Sequel Pro started as a fork of the open source CocoaMySQL project. It is the self proclaimed “Best Looking MySQL Database Management App for the Mac OS X“, going by the screenshot below they wouldn’t be wrong, it is sleek and cool.

Help and Support
Sequel Pro Support
Download
Sequel Pro Downloads Page

SQL Buddy (Web-App)

SQL Buddy
This cool lightweight ajax powered database management tool is amazing. It is very, very easy to install, unzip the folder to your server and thats it. As for upgrading, delete the older version and replace with the new version, simple. Being powered by Ajax, it is browser friendly so you can use normal browsing actions like “bookmark”, “back” and “refresh”. There is also a useful range of keyboard shortcuts, which you can view here: SQL Buddy Keyboard Shortcuts.

Help and Support
SQL Buddy Support
Download
SQL Buddy Downloads Page

EMS SQL Manager (Win)

EMS SQL Manager
EMS SQL Manager is a fairly high performance tool for database administration and development. Features include MySQL triggers, views, stored procedures and functions, InnoDB foreign keys, it allows you to create/edit all MySQL database objects, design databases visually, run SQL scripts, import and export database data, manage users and their privileges and has many other useful features. There is a commercial version, but download the free version, its fairly powerful and offers plenty of features.

Help and Support
EMS SQL Manager Support
Download
EMS SQL Manager Downloads Page

Navicat Lite MySQL Admin Tool (Win, OS X, Linux)

Navicat Lite MySQL Admin Tool
Navicat is a fast, reliable and popular (millions of downloads) Database Administration tool purpose-built for simplifying database management and reducing administrative costs. Designed to meet the needs of database administrators, developers, and small and medium businesses, Navicat is built with an intuitive GUI which lets you create, organize, access and share information in a secure and easy way. Navicat Lite is available as a free download only for non-commercial customers.

Help and Support
Navicat Support
Download
Navicat Downloads Page

DreamCoder for MySQL (Win)

DreamCoder for MySQL
DreamCoder works with all MySQL versions from 3.23 to 6.0 and it supports all MySQL features including tables, views, procedures, functions, triggers, InnoDB Tables, foreign keys , UDFS, BLOB Types, etc. It also includes the most advanced options for data manipulation, build queries visually, execute queries, script execution, reports generation, database monitoring and export and import data to/from most popular file formats. DreamCoder for MySQL is easy to use for the novice MySQL user and is powerful enough for any expert user.

Help and Support
DreamCoder Support
Download
DreamCoder Downloads Page

Toad MySQL (Win)

Toad MySQL
Toad for MySQL is a database development and administration tool that provides productivity features for rapidly creating and executing queries and managing complex database systems. It provides utilities to compare, extract and search for objects, manage projects, import/export data and administer the database. Changes to the interface recently has been the the introduction of the “Tabbed Document Interface”, this means you can view a fully maximized window without the need for you to constantly resize, arrange, or tile the windows.

Help and Support
Toad MySQL Support
Download
Toad MySQL Downloads Page

DB Tools Manager Professional (Win)

DB Tools Manager Professional
DBManager is a fairly powerful application data management data management. With builtin support for MySQL, PostgreSQL, Interbase/Firebird, SQLite, DBF tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC database engines, making it one of the most advanced database application. The free edition (Standard Edition) has a minimun set of features that makes it ideal for new database users to perform basic database management, you can use this for non commercial use.

Help and Support
DB Tools Manager Forum
Download
DB Tools Manager Downloads Page

MyCon – MySQL GUI (Win)

MyCon – MySQL GUI
MyCon is an extremely powerful windows based graphical user interface, MySQL GUI, for administering and working with MySQL server databases. This easy to use MySQL GUI front-end client includes all the functionality and tools you will need to be productive in MySQL as a DBA, developer, manager, report writer or a general end-user.

Help and Support
MyCon – MySQL GUI Forum
Download
Free MyCon Downloads Page

MyDB Studio (Win)

MyDB Studio
MyDB Studio is a complete collection of powerful tools for MySQL Server administration. MyDB Studio (freeware) is designed for MySQL developers and administrators to rapidly manage, create and execute queries and develop SQL and Php code more efficiently. Keep an eye out for the upcoming Adobe Air version of MyDB Studio, that will be cool.

Help and Support
MyDB Studio FAQ
Download
MyDB Studio Downloads Page

MySQL Sidu (Win)

MySQL Sidu
MySQL Sidu is a FREE MySQL client that works via your web browser, it is simple, intuitive and an easy database tool to use. Sidu is not as powerful as other similiar tools but packs enough punch to merit its place in this list, its features may be limited but it does do exactly what the name says (Sidu stands for Select Insert Delete Update).
You can also use a Firefox Extension in conjunction with Sidu that enables you to insert data from text fields & textareas in a webpage. View the details here: Firefox addon for MySQL Sidu.

Help and Support
MySQL Sidu Support
Download
MySQL Sidu Downloads Page

apache friends – xampp (Win, Mac, Linux, Solaris)

apache friends – xampp
It’s not easy to install an Apache web server and it gets harder if you want to add MySQL, PHP and Perl. XAMPP is an easy to install Apache distribution containing MySQL, PHP and Perl. It is really very easy to install and to use – just download, extract and start.

Help and Support
xampp Support
Download
xampp Downloads Page

Further MySQL Links

Finally, here are some further general references to MySQL and MySQL-related resources.

Via: smashingmagazine.com/

Fonte:http://speckyboy.com/

Posts tagged ‘mysql’

Importar dados para MYSQl

web development

Script simples para backup de BD MySQL

Backup MySQL em servidor remoto

Oracle compra Sun