Writing a trigger to generate uuid in a table
If you never had a field containing uuid before in MySQL table, now you can add a new field default is null. Then create a trigger so that every time there’s a new record inserted, the uuid will be automatically generated for that row. Here’s how you write a trigger to add uuid to a field “user_api_key” in the table “api_users” If you run into this error, make sure you don’t duplicate the trigger. This version of MySQL doesn’t yet support ‘multiple triggers with the…
Setting up MySQL replication in V5.6.17
Setting up MySQL replication In this article the Master is 210.126.187.015 and the Slave is 210.126.187.016 On the Master, edit /etc/mysql/my.cnf (my.ini in Windows): 1) server-id = 126187015 # choose a unique integer 2) log_bin = /var/log/mysql/mysql-bin.log 3) bind-address = 0.0.0.0 # listen on all interfaces (by default 127.0.0.1 is used) 4) binlog_do_db = mydb # replicate only the ‘mydb’ database On Windows environment: 1) server-id = 127001 # choose a unique integer 2) log_bin = C:/Documents and Settings/JohnDoe/My Documents/Application Data/MySQL/MySQL56/mysql-bin.log 3) bind-address = 0.0.0.0…
How to insert an image to MySql database with php
A BLOB is a binary large object that can hold a variable amount of data. There’re 4 datatypes that you can select for a blob field, depending how much storage of an image you’ll need. TINYBLOB A binary large object column with a maximum length of 255 (2^8 – 1) characters. BLOB A binary large object column with a maximum length of 65535 (2^16 – 1) characters. MEDIUMBLOB A binary large object column with a maximum length of 16777215 (2^24 – 1) characters. LONGBLOB A…
SQL Stored Procedures
Sometimes in your Database Admin career path, you’ve got to create a procedure to save your time and reduce loads of work on server. For example, everyday you need to run a report of how many customers have signed up for a newsletter, or you need to pull out a list of customers who purchased items for the past 12 hours… In these cases, you don’t want to re-write a SQL query every time; instead, you’ll create a procedure and store it in the Database…
How to change the storage engine of a MySQL table
This post will show you how to change a storage engine of a MySQL table to another engine. 1) For SQLYog software users: select a table you want to change the storage engine, then click on Table tab, select Alter Table. Then click on Advanced Properties. You will see a dialog box as seen below, select Table type drop down box, and select an engine you wish for that specific table. 2) For PHPMyAdmin users: select database, select a table that you want to change…