mysql

Where is MySQL / MariaDB storage location by default on CentOS 7?

ByEric Ma

Where is MySQL / MariaDB storage location by default on CentOS 7? No special configuration to the MariaDB from official repository of CentOS. On CentOS 7 Linux it is usually by default /var/lib/mysql But here I give you another “hacky” way to find it out. The method is to find out the mysql daemon mysqld’…

How to write a SQL to replace strings in a column in a MySQL database table?

ByEric Ma

How to replace strings in a column in a MySQL database table? For example, I would like to replace http://www.systutorials.com with https://www.systutorials.com in a content column of a table post. You may use a SQL statement as follows. update post set content = replace(content, ‘http://www.systutorials.com’, ‘https://www.systutorials.com’) where content like ‘%http://www.systutorials.com%’

how to integrate MySQL client into self designed database

ByWeiwei Jia

Currently, I need to integrate MySQL client into SQLE as SQLE’s client. And then, we just need design/implement a translation layer to transfer MySQL protocol into SQLE engine part (KV+ internal exactly). Mysql-Proxy could solve this problem but it is complex to be only a MySQL server. Therefore, I remoduled MySQL-proxy to be more simple…

HTML form generation from the database and store value into the database

ByQ A

I have a “t_form” table and a “t_attribute” table. It looks a little bit like below. Form table form_id | form_name | description ———————————– 1 | Laptop | Possible attributes are Model, Screen Size, OS, Cd Player 2 | Mobile | Possible attributes are Model, OS 3 | Tablet | Possible attributes are Model, Screen…

How to find out and change the storage engine of tables in MySQL

ByQ A

How to find out and change the storage engine of tables in MySQL databases? Find out the storage engine of a table in a database: SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘database’ AND TABLE_NAME = ‘table’ Change the storage engine of a table: ALTER TABLE table ENGINE = type type can be innodb or…

How to change strings in MySQL tables

ByQ A

How to change strings in MySQL tables? e.g. I want to change domain.com to www.domain.com. Use the REPLACE functions of MySQL: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace One example is like this: UPDATE table SET field = REPLACE(field, ‘domain.com’, ‘www.domain.com’) WHERE field LIKE ‘%domain.com%’ The WHERE clause is not needed but can make execution faster.

MySQL at Facebook

ByQ A

Facebook uses lots MySQL databases. Any information about how Facebook scales MySQL? Some information on the Web: MySQL at Facebook’s page https://www.facebook.com/MySQLatFacebook?filter=1 A post by Ryan Thiessen, Database Operations at Facebook on Quora: http://www.quora.com/Facebook-Engineering/How-does-Facebook-structure-MySQL-so-that-it-is-robust-and-scalable And more: http://mashable.com/2011/12/15/facebook-timeline-mysql/ http://gigaom.com/2011/12/06/facebook-shares-some-secrets-on-making-mysql-scale/ http://www.wired.com/wiredenterprise/2011/12/facebook-timeline-anatomy “A lot of people are surprised that for this shiny new thing for Facebook, we’re using…

Cache at Facebook

ByQ A

About caching system at Facebook. According to: https://www.facebook.com/notes/facebook-engineering/monitoring-cache-with-claspin/10151076705703920 Facebook has two major cache systems: Memcache, which is a simple lookaside cache with most of its smarts in the client, and TAO, a caching graph database that does its own queries to MySQL. The NSDI’13 paper introduces more about Memcache: https://www.usenix.org/conference/nsdi13/scaling-memcache-facebook The USENIX ATC’13 paper introduces…

How to repair a MySQL table?

ByQ A

After a server crash and restarting, MyBB reports a SQL Error as follows: MyBB SQL Error MyBB has experienced an internal SQL error and cannot continue. SQL Error: 145 – Table ‘./mybb/mybb_sessions’ is marked as crashed and should be repaired Query: SELECT * FROM mybb_sessions WHERE sid=’40021925bd0494ea31…’ AND ip=’x.x.x.x’ LIMIT 1 The dababase is MySQL….

How to Connect to MySQL in JSP

ByEric Ma

We use tomcat as the container used for instructions in the post. 1) Download the driver mysql-connector-java-*.*.*-bin.jar and put it into WEB-INF/lib/, and remember to restart tomcat. 2) The example code as follows. String driverName=”com.mysql.jdbc.Driver”; String userName=”username”; String userPasswd=”password”; String dbName=”dbname”; String tableName=”tablename”; String url=”jdbc:mysql://localhost/”+dbName+”?user=”+userName+”&password=”+userPasswd; Class.forName(“com.mysql.jdbc.Driver”).newInstance(); Connection conn=DriverManager.getConnection(url); Statement statement = conn.createStatement(); String sql=”SELECT *…