background preloader



Overview at Maatkit MySQL Backup Tool Rational Relational: Emulating The Missing RENAME DATABASE Command in MySQL I'll approach this topic from another direction. I had to perform a task which required moving tables between schemas on the same MySQL server. One way of doing this would be: Dumping the original table to disk.Creating new empty table on the target schema.Importing table from disk into the target schema. But, a simpler way exists! As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another:RENAME TABLE current_db.tbl_name TO other_db.tbl_name; Which works almost instantaneously and does what it is supposed to do. Using this command, it is easy to emulate the missing RENAME DATABASE command. Create the new schema.Perform a query to generate the move commands from the source to the target schema:SELECT CONCAT('RENAME TABLE ',table_schema,'.' Some notes: If you're using InnoDB foreign keys, worry not. The move command also moves the .idb files between the database directories, if you're using innodb_file_per_table. Hope this helps.

10 Ways to Automatically & Manually Backup MySQL Database Mar 15 2009 MySQL is one of the most popular open source database management system for the development of interactive Websites. If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there). There are several ways to backup MySQL data. 1. Many of users use Amazon S3 to backup their mysql databases. 2. 15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz This post will show you how to backup MySQL Database automatically if you are a linux user. 3. 4. mysqldump ---user [user name] ---password=[password] [database name] > [dump file] In this article we’ll look at how to backup our databases using the mysqldump utility that comes with MySQL. 5. Here’s a PHP snippet that outputs your database as XML. 6. Execute a database backup query from PHP file. 7. 8. 9. 10. 11. Worth Reading Homepage

10. Concurrencia 10.1 Introducción Como se mencionó anteriormente una de las grandes ventajas de emplear bases de datos y más especificamente de emplear un dbms es que se procure un buen manejo de la información, garantizando que todas las operaciones sean "ácidas", es decir, que cumplan con Atomicity: que las transacciones sean atómicas, se vean como una sola unidad Consistency: que los datos realmente estén bien relacionados y que no existan problemas de falta de información o falta de confiabilidad en los datos Isolation: aislamiento, separar las operaciones de distintas conexiones/usuarios unos de otros Durability: que se garantice la persistencia de los datos. Durante esta sección se tratan de abordar los conceptos de atomicity, consistency e isolation. 10.2 Transacciones 10.2.1 Definición Una transacción es una unidad de programa que accesa y posiblemente actualiza varios elementos de datos. 10.2.2 Estados de transacción 10.2.3 Programación Notas: 10.3 Control de concurrencia 10.3.1 Introducción Deadlock

MySQL Cluster Manager 1.1.2 – creating a Cluster is now trivial @ Andrew Morgan’s MySQL Cluster Database Blog MySQL Cluster Manager 1.1.2 is now available to download and try from Oracle E-Delivery (select “MySQL Database” as the product pack). Something that’s new and really cool in the new version is that you can download a version of MCM that actually includes the MySQL Cluster software itself and then you can have MCM automatically define, create and start a single-host cluster deployment for you with just the command “mcmd –bootstrap”. This post aims to show that it’s really as simple as that! I’ve been playing with Windows recently and so I’ll use that for this example but things would be very similar on other platforms. Step 1 Download from E-Delivery and extract the zip file Step2 Start your first cluster! PS D:AndrewDocumentsMySQLmcm> binmcmd --bootstrap MySQL Cluster Manager 1.1.2 started Connect to MySQL Cluster Manager by running "D:AndrewDocumentsMySQLmcmbinmcm" -a NOVA:1862 Configuring default cluster 'mycluster'... That’s it! So how much simpler is this than doing it by hand?

Live MySQL Conference, London, Oct 24th and 25th, 2011 Featured Speakers Peter ZaitsevPercona,CEO/FounderBaron SchwartzPercona,Chief Performance ArchitectDomas MituzasFacebook,Database EngineerAndrew AksyonoffSphinx,CEO/FounderYoshinori MatsunobuDeNA,Principal Infrastructure ArchitectMatthew A YonkovitPercona,Prinicipal Architect more speakers... Discover the Power of MySQL » Percona Live is an intensive two-days MySQL summit. There are many tracks of expert speakers, including Percona consultants and hand-picked guests. The sessions are 100% technical—even the sponsored sessions. Learn what works from leading companies who use MySQL.Hear how to drive down costs and improve performance with innovative solutions.Discuss your unique challenges and discover options for solving them.Meet face-to-face in the "hallway track" and make lasting connections.Engage the best and brightest in a lively job market. At Percona Live, you'll benefit from the expertise of the most accomplished system architects and developers in the business. Speaker List

Announcement of Percona XtraDB Cluster (alpha release) January 9, 2012 by Vadim Tkachenko30 Comments I am happy to announce the availability of alpha release of our new product Percona XtraDB Cluster. Percona XtraDB Cluster is High Availability and Scalability solution for MySQL Users and based on Percona Server 5.5.17 Percona XtraDB Cluster provides: Synchronous replication. Percona XtraDB Cluster is fully compatible with MySQL or Percona Server in the following meaning: Data compatibility. Percona XtraDB Cluster is based on Percona Server with XtraDB and includes Write Set REPlication patches. The Galera library is developed by Codership Oy company and Galera 2.x supports such new features as: Incremental State Transfer, especially useful for WAN deploymentsRSU, Rolling Schema Update. Please note, this is an alpha release, not suitable for production yet. Links: Previous posts on this topic:

5 Ways to Boost MySQL Scalability There are a lot of scalability challenges we see with clients over and over. The list could easily include 20, 50 or even 100 items, but we shortened it down to the biggest five issues we see. 1. Tune those queries By far the biggest bang for your buck is query optimization. Queries can be functionally correct and meet business requirements without being stress tested for high traffic and high load. Enable the slow query log and watch it. Also: Why generalists are better at scaling the web 2. Master-master active-passive replication, otherwise known as circular replication, can be a boon for high availability, but also for scalability. If you’re setting up replication for the first time, we recommend you do it using hotbackups. Keep in mind MySQL’s replication has a tendency to drift, often silently from the master. Related: Why you can’t find a MySQL DBA 3. It sounds very basic and straightforward, yet there are often details overlooked. 4. What is underneath your database? 5.

Scalability improvements in MySQL 5.6 At MySQL Connect last weekend the MySQL 5.6 was released as a Release Candidate. It contains a large set of improvements over MySQL 5.5. In this blog I will focus on what has changed in the area of scalability. There are four main areas of improvement of scalability in the MySQL 5.6:1) Splitting InnoDB Kernel mutex2) The G5 discovery3) Splitting LOCK_open mutex4) Binlog group commit In InnoDB as in so many other products there was an infamous kernel mutex. Already early in the MySQL 5.6 development an effort in the InnoDB development team led by Sunny Bains, took the kernel mutex and split it into around 10 new mutexes. The G5 discovery refers to a name we gave a patch internally. Another sort of kernel mutex is the LOCK_open mutex in MySQL. Finally this idea also became a new addition to the MySQL 5.6 release. What we have discovered with MySQL 5.6 and its benchmarking is that the standard Sysbench has a limiting factor. Each step has two phases, a queue phase and an execution phase.