In order to use SQLite 3 with Java, you need to do the following steps on your Linux or Windows box. I have deliberately kept off specific commands. Install SQLite3 from your Linux distribution repository. You can also download the Windows edition if you use Windows. Visit https://bitbucket.org/xerial/sqlite-jdbc and download the JDBC driver. I downloaded … Continue reading Use SQLite with Java
Oracle Data Modeler
Look for it on google. It is a great way to design databases. Added bonus – It creates the scripts to create your database for you.
Dump MySQL tables to text file for backup
$ mysqldump address --add-drop-database --add-drop-table --add-locks --complete-insert --flush-privileges -R --triggers > /home/user/addressdump.mysql Repeat above for every table in your system. You can also put it in a batch file for a series of tables. This is the only way to restore databases if you upgrade to MySQL to a major version. This was an exception, … Continue reading Dump MySQL tables to text file for backup
/var/lib/mysql folder permissions in Linux
/var/lib/mysql (drwx-------) /var/lilb/mysql/mysql (drwx------) files in this folder should be (rw-rw----) Same for all databases folders Owner /var/lib # chown -R mysql.mysql mysql
Forgot MySQL password
# su # /etc/init.d/mysql/bin/sqfe_mysqld -user=-mysql –skip-grant-tables –skip-networking& # /usr/local/mysql/bin/mysqladmin -u root flush_privileges password 'newpassword' # /etc/init.d/nmysql server restart Now you can use the new root password
Install and setup GTK+ and MySQL in Windows
Install Dev C++ (devcpp) Install gtk-dev-2.8.6.DevPak Install mysql.devPak Install mysql gtk_win32_devel_2.8.6_rc3.exe gtk-2.8.6-runtime.exe mysql-4.0.14b-win-noinstall.zip In Dev C++, do the following steps: Add all of Dev_cpp/ lib to Project->ProjectOptions Add libmysql-lib to above also Copy MySQLLib.dll into windows/system32 and this gets rid of the mysql DLL runtime error. Remove files not recognized during build such as ../lib/jpeg-bcc.lib. … Continue reading Install and setup GTK+ and MySQL in Windows
Huge MySQL table. Use index to speed queries
Table has 2500000 records. Normal search takes 38 seconds. After indexing it takes only .16 seconds. > alter table mtp40 add index sn(sort_name, g_srno); Above creates index sn which gives the query search, above boost. > alter table mtp40 drop index sn; This drops the index 'sn' from the above table.
Small step to prevent SQL injection in MySQL
Use mysql_real_escape_string($string_var) to be used for every string variable. Eg: $tip_no = mysql_real_escape_string($_POST['tip_no']); Then build the update string using the result of above statement.
Pagination in MySQL
Select * from tips order by tip_no limit 25,4; where 25 is the start row of the record fetched and 4 is the number of records to be fetched.
Start and Stop MySQL service
# /sbin/service mysqld start # /sbin/service mysqld stop