background preloader


Facebook Twitter


How To Set Up Database Replication In MySQ. This is a "copy & paste" HowTo!

How To Set Up Database Replication In MySQ

The easiest way to follow this tutorial is to use a command line client/SSH client (like PuTTY for Windows) and simply copy and paste the commands (except where you have to provide own information like IP addresses, hostnames, passwords,...). This helps to avoid typos. How To Set Up Database Replication In MySQL Version 1.1 Author: Falko Timme <ft [at] falkotimme [dot] com> Last edited: 01/14/2006 This tutorial describes how to set up database replication in MySQL. In this tutorial I will show how to replicate the database exampledb from the master with the IP address to a slave. Both systems have MySQL installed, and the database exampledb with tables and data is already existing on the master, but not on the slave. I want to say first that this is not the only way of setting up such a system. 1 Configure The Master First we have to edit /etc/mysql/my.cnf.

MySQL Query Analyzer - Improving SQL Query Performance. MySQL Gotchas. It's not a bug - it's a gotcha.

MySQL Gotchas

A "gotcha" is a feature or function which works as advertised - but not as expected. When working with the MySQL ™ database server I have repeatedly encountered situations where the results of various actions have been unexpected and/or contrary to the behaviour generally expected of an SQL relational database. The cause can usually be traced to implementation details which are documented in the manual. I have created this list in order to further a better understanding of the MySQL database server and hopefully save others unnecessary headscratching.

The MySQL database server is being continually improved. Note: this document contains many examples as they would appear in the MySQL command line client mysql ("mysql monitor"). 1.1. In SQL NULL represents the the absence of a value. Example 1. CREATE TABLE null_1 ( id INT NOT NULL, text1 VARCHAR(32) NOT NULL, text2 VARCHAR(32) NOT NULL DEFAULT 'foo'); Example 2.

The manual explains this behaviour thus: Note. Make MySQL faster in one hour. There may be a simple way to improve InnoDB performance on SMP servers.

Make MySQL faster in one hour

The benefit is not as great as that obtained by using the smpfix from Google or Percona, but the change is much simpler. It is also portable so it makes MySQL faster on non-x86 platforms. I want to hear from people who test this with real workloads on servers with 8+ cores or who do any type of testing on platforms other than Linux/x86. 15.3.4 Using MySQL and me. MySQL Cookbook. To Insert or To Update. We talked about duplicating with MySQL earlier, and another MySQL tip worth pointing out is on inserting or updating in one query.

To Insert or To Update

The idea is that there will be times when you would like to update a record if it exists, or insert if it does not exist. This becomes especially true in the world of web 2.0 and API’s where the return value may come over a delay. A real world example where this could come up is in payment processing. Let’s say we’re using the Paypal API to record a payment. The API will talk to us a few times. The brute force approach to this problem is to do a read to see if the record exists, and update or insert accordingly: $sql = 'SELECT TransId FROM Sales WHERE TransId = 123'; $rs = $db->query($sql); if(mysql_num_rows($rs) == 1) { // do an SQL UPDATE } else { // do an SQL INSERT } And while this gets the job done, it would be nicer if we could work it all in to one query.

If the transaction exists, this query will just change the Status from ‘Pending’ to ‘Paid’. PHP MySQL Tutorial. MySQL Quick Admin - Free MySQL Web GUI. PHP &amp; MySQL : Generating an RSS feed - Presentations &amp; A. Storing Hierarchical Data in a Database [PHP &amp; MySQL Tutoria. This article was written in 2003 and remains one of our most popular posts.

Storing Hierarchical Data in a Database [PHP &amp; MySQL Tutoria

If you’re keen to learn more about mastering database management, you may find this recent article on MySQL of great interest. Whether you want to build your own forum, publish the messages from a mailing list on your Website, or write your own cms: there will be a moment that you’ll want to store hierarchical data in a database. And, unless you’re using a XML-like database, tables aren’t hierarchical; they’re just a flat list. You’ll have to find a way to translate the hierarchy in a flat file. Storing trees is a common problem, with multiple solutions.

In this article, we’ll explore these two methods of saving hierarchical data. This article contains a number of code examples that show how to save and retrieve data. The first, and most elegant, approach we’ll try is called the ‘adjacency list model’ or the ‘recursion method’. As you can see, in the adjacency list method, you save the ‘parent’ of each node. <? <? Common Queries. Common MySQL Queries Basic aggregation Last updated 05 Jan 2013 Aggregate across columns Last updated 09 Sep 2009 Aggregates across multiple joins Given a parent table and two child tables, a query which sums values in both child tables, grouping on a parent table column, returns sums that are exactly twice as large as they should be. CREATE TABLE packageCredit ( packageCreditID INT, packageCreditItemID INT, Last updated 22 Feb 2013 Aggregates excluding leaders You have a table of grouped ranks ... Last updated 21 May 2009 Aggregates of specified size Find the values of a table column c1 for which there are a specified number of listed values in another column c2.

All X for which all Y are Z You have an election database with tables for candidates, parties and districts. CREATE TABLE districts ( district char(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO districts VALUES ('Essex'),('Malton'),('Riverdale'),('Guelph'),('Halton'); Avoiding repeat aggregation. Optimizing your MySQL Application [PHP &amp; MySQL Tutorials] I’ve attempted to keep my queries as simple as possible, but I assume that you have a basic understanding of databases and the SQL language (more specifically, MySQL’s implementation of it). I also assume you have MySQL 3.23, as a few of these queries may not work on 3.22. If you don’t have MySQL 3.23 yet, I highly recommend you install it if possible, as some of the performance increases are significant.

What are Indexes? Indexes are organized versions of specific columns in your tables. MySQL uses indexes to facilitate quick retrieval of records. Dan Winchester - MySQL date_format. MySQL 5.0 Reference Manual.