This is how you can backup you MySQL database(s) and stored procedures
# mysqldump --routines <dbname>
Or you can backup only the stored procedures
# mysqldump --no-create-db --no-create-info --no-data --routines <dbname>
Tag: MySQL
mysql alternative to PHP substr_count function
The substr_count function in PHP counts the number of substring occurrences. This post describes how to create a mysql stored function to behave just like PHP’s substr_count function.
This function can be created from your mysql console
delimiter || DROP FUNCTION IF EXISTS substrCount|| CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS TINYINT(3) UNSIGNED LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA BEGIN DECLARE count TINYINT(3) UNSIGNED; DECLARE offset TINYINT(3) UNSIGNED; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL; SET count = 0; SET offset = 1; REPEAT IF NOT ISNULL(s) AND offset > 0 THEN SET offset = LOCATE(ss, s, offset); IF offset > 0 THEN SET count = count + 1; SET offset = offset + 1; END IF; END IF; UNTIL ISNULL(s) OR offset = 0 END REPEAT; RETURN count; END; || delimiter ;
Usage
Example 1
SELECT substrCount('/this/is/a/path', '/') `count`;
`count` would return 4 in this case. Can be used in such cases where you might want to find the “depth” of a path, or for many other uses.
This function is great to count the content of mysql ENUM and SET field data types.
Example 2
SELECT substrcount(
`tablename` , ','
) as tablename
FROM `tablename`
where substrcount(
`tablename` , ','
);
The content of table named tablename is a comma separated list generated from mysql ENUM datatype
2000/2001,2001/2002,2002/2003,2003/2004,2004/2005,2005/2006,2006/2007,2007/2008,2008/2009,2009/2010
In Example 2 the result from this query would be 9, telling us that there are 9 commas in this tablerow.
Howto combine two columns into one in mysql
This is how you can combine (or concatenate or merge) data from two table columns into one column using a mysql SQL query.
SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename
The result would be “firstname lastname”
If you would like to insert this data into a new column
INSERT INTO tablename (full_name) SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename
MySQL search and replace
This is a simple SQL query to perform search and replace in a MySQL table
update tablename set fieldname = replace(fieldname,'search_for_this','replace_with_this');
mysql on a nondefault socket
It is sometimes necessary to run two instances of mysql, like in my case. I need a mysql database in addition to the one Zimbra uses. One solution to this problem is to run the mysql database on a non default socket. This can be done by changing the following line in my.cnf
my.cnf
[client] ... socket = /var/run/mysqld/mysqld2.sock
Restart the mysql daemon afterwards to activate the change.
In PHP it’s possible to avoid programming the nondefault socket info whenever we use mysql
php.ini
[MySQL] ... mysql.default_socket = /var/run/mysqld/mysqld2.sock
If you don’t have access to php.ini you have to program this in your PHP code
$dbcnx = @mysql_connect('localhost:/var/run/mysqld/mysqld2.sock',$username, $password);