background preloader

Mysql

Facebook Twitter

Ocaml

How To Set Up Database Replication In MySQ. This is a "copy & paste" HowTo! 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.1Author: Falko Timme 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 192.168.0.100 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. Then we restart MySQL: /etc/init.d/mysql restart mysql -u root -p Enter password: Now we are on the MySQL shell. quit; MySQL Gotchas.

It's not a bug - it's a gotcha. 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. Some gotchas described here are no longer relevant for the latest versions; in these cases the version numbers affected are noted at the top of each section. As a rule gotchas have been tested against the most recent stable versions from the 3.23.x, 4.0.x and 4.1.x series. 1.1. In SQL NULL represents the the absence of a value. Example 1. Example 2. Note. Make MySQL faster in one hour. There may be a simple way to improve InnoDB performance on SMP servers. 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.

The patch for MySQL 5.0 is at code.google.com. The change is to replace the mutex_t used in the InnoDB rw_lock_struct with a pthread_mutex_t. InnoDB implements a mutex (mutex_t) and a read-write lock (rw_lock_struct). Of course, you shouldn't take my word for it so I will provide a few results. Results for sysbench --test=oltp --oltp-read-write. Results for concurrent queries. 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.

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. Immediately after the user submits the payment, the API tells us a transaction id, and that the status is pending.

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 } PHP MySQL Tutorial. MySQL Quick Admin - Free MySQL Web GUI. PHP & MySQL : Generating an RSS feed - Presentations & A. Storing Hierarchical Data in a Database [PHP & MySQL Tutoria. This article was written in 2003 and remains one of our most popular posts. 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. There are two major approaches: the adjacency list model, and the modified preorder tree traversal algorithm. 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.

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 Cascading aggregates. 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.

With indexes, MySQL can jump directly to the records you want. Without any indexes, MySQL has to read the entire data file to find the correct record(s). Suppose we created a table called "people": CREATE TABLE people ( <br> peopleid SMALLINT NOT NULL, <br> name CHAR(50) NOT NULL <br> CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL As you can see, there’s no recognizable order to the “name” column whatsoever. Types of Indexes. Dan Winchester - MySQL date_format. MySQL 5.0 Reference Manual.