Increase Max PHP Upload size

It is often necessary to increase the upload size for PHP scripts. I find I have to do this with every Moodle installation but there are many reasons for increasing the upload size. Maybe you wrote your own PHP scripts that allow uses to upload files to your site or maybe you have a different program like Gallery, WordPress, or Joomla. What ever your reason it is very simple in Linux.

To increase the max upload size for PHP you have to edit the php.ini file with root privileges.

In Debian installations like Ubuntu you start your statement with sudo to evaluate your privileges to root or administrator.

In Ubuntu type this: sudo nano /etc/php5/apache2/php.ini
Press Ctrl and W to search for this variable: post_max_size
Now change the value to the number of Mb you want your site to accept as uploads like: 128M
Press Ctrl and W to search for this variable: upload_max_filesize
Change the value to the number of Mb you want your site to accept as uploads. Please note this should be the same as above such as: 128M
It may also be necessary to increase the execution time when you do this.
While still editing the php.ini file Press Ctrl and W and search for: max_execution_time
Now increase the value to something like: 600
Now save the file: Press Ctrl and O
Now you can exit the nano editor: Press Ctrl and X
Now just restart apache to load these new values: Type sudo /etc/init.d/apache2 restart

 

For other flavors of Linux such as Red Hat, Fedora, CentOS follow the same steps except you won’t use sudo. You can run the commands as root.

Posted in Computers & Technology, Uncategorized | Comments Off on Increase Max PHP Upload size

HTML Escape Characters

I never uses these enough to remember them so I though I would write them down for easy access and hopes that someone else might find it useful.

@ at sign @
` back apostrophe `
¦ broken vertical bar ¦
bullet
¢ cent sign ¢
© copyright sign ©
dagger &#134
dash
° degree sign °
÷ division sign ÷
double dagger
» double greather than sign »
« double left than sign «
ellipsis
½ fraction 1/2 ½
¼ fraction 1/4 ¼
¾ fraction 3/4 ¾
> greater than sign &#062
¡ inverted excallamation point ¡
¿ inverted questionmark ¿
[ left bracket [
{ left curly brace {
left double quote
left double quote
left single quote
< less than sign <
¬ logical not sign ¬
longer dash
± plus or minus sign ±
£ pound sign £
® registered trademark sign ®
] right bracket ]
} right curly brace }
right double quote
right double quote
right single quote
short dash
¹ superscript 1 ¹
² superscript 2 ²
³ superscript 3 ³
~ tilde ~
trademark
| vertical bar |
¥ yen sign ¥
Posted in Computers & Technology | Comments Off on HTML Escape Characters

A Quick Look At Linux

Linux is a UNIX like system created by Linus Torvalds in 1991, is one of the first true open source operating systems. An operating system is software that controls access to hardware, software and other resources on a computer. Much like Windows or Apple’s OS it has an interface that is user friendly and provides users an easy way of accessing programs and files stored on a computer. Linux is an extremely secure operating system and as of this writing is not as susceptible to viruses as windows. The main reason for this is because of two main reasons. The first and foremost reason is the security built into Linux makes it very hard for viruses to be able to attack and install themselves. The second main reason is that when Bill Gates created Microsoft the majority of the programming community believed software code should be shared. Bill Gates wanted to be paid for his hard work and according some didn’t mind using ideas from code that was in the shared community. However you look at it most programmers back then disliked Gates because he wanted to make a living off writing software. Linux was created as the Anti-Windows and is used widely among the programming community and open source community.

The great thing about Linux is that it can be downloaded and installed on as many computers as you want and it doesn’t cost any anything. It is free to use and distribute. The idea behind Open Source is that you make you code available to the general public free of charge so they can modify it to suit their needs. Because everything must be driven by money the open source community believes that the profits should come in the form of service. You may use my code free of charge but I offer my services to help you install, trouble shoot, etc at a charge. Often most non open source communities sell the ability to use their software but offer technical support for free as long as you are paid or paid for the software.

There are many flavors of Linux to use Red Hat, SuSE, Mandrake, Ubuntu, and others. Red Hat and SuSE are known for their distributions in the commercial environment, both server and desktop platforms can be purchased giving security updates, technical support, etc. Ubuntu is quickly gaining its share in the computer world due to its ease of use and free nature. At this point it may even have a larger share then any other Linux type. Ubuntu offers server, desktop, and cloud version free of charge. Ubuntu now has a major commercial backer, Canonical who is supporting Ubuntu in many countries. I would guess that Red Hat and Ubuntu currently make up the majority of Linux based computers. A full list of Linux distributions can be found here.

If you want to try out Linux but don’t want to install anything on your computer most distributions have a Live CD you can download free of charge. Just burn the downloaded image onto a CD or DVD and put it in your computer before it boots up. It will run Linux from the CD and you can try it out without ever having to install Linux.

From my usage I have found Ubuntu to be the easiest and fastest. Linux is a lightweight operating system to start with and Ubuntu capitalizes on that. My Ubuntu server will boot up and be running in just a few seconds. My Ubuntu desktop/Laptops boot a little slower but still faster then most Operating Systems like Windows or the Mac OS.

All Linux distributions have a plethora of free software you can install. From office productivity software like Open Office to Image Multiplication programs like GIMP and Darktable. If there is a type of software you want you can be sure you will find it for your Linux installation.

The downside to Linux is that it doesn’t have a lot of commercial developers creating software for it. For example Adobe Photoshop or Microsoft Office don’t run natively in Linux installations because their companies don’t create installation packages for Linux. Instead to run those types of commercial programs you will need to run them through a program called WINE which acts as a compatibility layer between Linux and the software. It doesn’t always work 100% and may take a little tweaking to get it functioning correctly.  You can always find a free version that may be just as good or almost as good as the commercial programs but sometimes you want to use the software you are use to. So because you can’t run these natively this is a downside.

The other downside is that you will need to learn a new operating system. Lucky it will feel similar to Microsoft Windows or the Mac Os so there is just a small learning curve.

 

Posted in Computers & Technology | Comments Off on A Quick Look At Linux

A Quick Look at DHCP

This is a quick overview of how DHCP works

DHCP is a service that assigns IP address to client computers. Even DHCP sends packets using the transport layer it is considered an application layer protocol because the data is actually handled in the application layer.

Without a DHCP server on a network a network administrator would have to go around to each client computer and manually and statically set an IP address on every computer. Windows Server, Linux, routers, and many other server type operating systems almost all have the ability to act as a DHCP server. The information here is fairly general but is written from a Windows environment perspective but still a lot will apply outside of the windows environment.

How DHCP works: It takes a 4 broadcast packet exchange between the client and DHCP server. The client and server must use broadcast, which means the packet is sent to every computer on the network. The Packets must be sent to every compute because neither the client nor server knows how to reach the other. This is because the client doesn’t have an IP address or now the IP address of the DHCP server yet.

The Packet Exchange:

  1. The Client broadcasts a DHCP Discover packet to every computer on the network asking for a DHCP server to send back configuration information including the MAC Address of the DHCP server.
  2. The DHCP server then broadcasts a DHCP Offer packet to every computer which includes the configuration information.
  3. When the client receives the Offer packet it will broadcast a DHCP Request packet which basically says it accepts the configuration information but will not use it unless it receives the final word back from the DHCP server saying it can. In this step if the client received multiple Offer packets from different DHCP servers it would also send back a decline packet to the servers it does not choose and because these are also broadcasts it would include the server’s MAC address in the packet to differentiate between the servers.
  4. When the DHCP server receives the Offer packet it will broadcast it last packet for the client called a DHCP Pack packet. This packet tells the client it may go-ahead and us the configuration information that was sent in the Offer packet. Once the client receives this packet it sets its IP address and any other settings sent by the server and now the client can communicate with computers on the network without having to broadcast every packet.

DHCP Lease/Renew:
Unless configured to do so a DHCP server doesn’t hand out permanent IP addresses, instead it leases the IP address to the client with a set expiration time. Once that time has reaches 50% the client will try to renew the IP address by sending out a DHCP Request packet again but it can use the IP address of the DHCP server instead of a broadcast because it now knows the IP address of the DHCP server. If the server doesn’t respond back the client will try again when 87.5% of the time is used up. If the server still doesn’t respond the client will try one last time when 100% of the time is used up and if the server still doesn’t respond the client will start the whole process again from step one by broadcasting a Discover packet for any server to respond. If the server did respond at any of those points it would have sent out a DHCP ACK packet saying the client may renew its IP address or a DHCP NAK packet saying it may not renew its IP address.

Specific Windows settings/commands
Netsh – Used by Windows command prompt to manage DHCP
Scope – The IP address range used by a DHCP server. Different settings can be applied for different scopes.
Super Scope – Used by Windows DHCP server to combine two network scopes on a DHCP server into a single logical scope.
Multicast Scope – scope within the IP range 224.0.0.0 – 239.255.255.255 used to send packets to a group of computers instead of one IP.
Reservation – Used by DHCP servers set client reservations. A reservation is where the DHCP server assigns the same IP address to the same client based on the MAC address of the client’s network card. Different settings can be applied for different reservations.
DHCP Vender Class – Used by Windows DHCP server to use different scopes for a client based on the client’s operation system.
DHCP User Class – Used by Windows DHCP server to use different scopes for a client based on how the client is connected to the network or the class ID on the client computer.
Class ID – Used by Windows to set up DHCP groups. Each client can be set up with a different class ID group by using the ipconfig /setclassid [ID of your choice] command. The DHCP server will use a scope defined for that User Class.
Managing your Windows DHCP Server
Repair a corrupt DHCP database – Event viewer may give you message saying that the DHCP database is corrupt. To fix this Backup you DHCP Database and then run jetpack from the command line with in the directory containing the dhcp.mdb and temp files. Example: jetpack dhcp.mdb tempfile
DHCP Logs are stored by the day of the week and they can tell you about problems your DHCP server may have.

Statistics, Loggs & Codes:
Within the DHCP mmc snapin you can view DHCP statistics which show you the percent of IP addresses used as well as some other information.
The Performance tool can be used to view the number of Discovers/sec which if high could mean your lease time is too short. It can also show you the number of Declines/sec which could mean you have a rouge DHCP server on your network trying to hand out IP addresses.

Windows DHCP server Log ID codes:
00 – audit log started
01 – audit log stopped
02 – audit log stopped due to disc space too small
10 – New IP address assigned to client
11 – Lease was renew by client
12 – A Lease was released by client
13 – Certain IP address is already in use on the network
14 – Client could not obtain lease – no available address in scope
15 – DHCP lease denied
16 – DHCP Lease deleted
17 – Lease expired
50+ – Messages correspond to rouge Servers or unauthorized DHCP serves

Event Viewer ID Codes:
1040 Successfully restarted the DHCP database
1044 DHCP server is authorized
1045/1046 DHCP server is not authorized
1052 Duplicate IP
1056 Dynamic DNS credentials not configured
1059 Could not contact a domain controller for authorization

Useful command line tools:
Renew your IP address: ipconfig /renew
Release your IP address: ipconfig /release
View your IP configuration: ipconfig /all
Test connectivity to another computer: Ping [IP address of remote computer] example: ping 192.168.0.1

Posted in Computers & Technology, Uncategorized | Comments Off on A Quick Look at DHCP

Setup Moodle on Ubuntu Tutorial

I have done several Moodle installs on RedHat, CentOS, and Ubuntu Server. By far I like Ubuntu the most. Ubuntu Server is simple to setup and use, plus it is a much lighter weight server then the others and boots much faster. I also usually have fewer issues with setting up Moodle on Ubuntu. Don’t get me wrong RedHat and CentOS (which are the same thing) are great operating systems and are not difficult either, it is just hard to beat Ubuntu.

This tutorial may seem long and difficult but don’t get discouraged. It is a lot more simple then is seems.  I have included all the Ubuntu Server commands you should need.  At the end of this post I have included some Linux commands that I have found useful when using Ubuntu Server. A note about Ubuntu, often you need to run a command as root or administrator. This is knows as the super user and is accomplished by prefixing the command with sudo. Example: sudo apt-get install package

Here are the overall steps you need. Look below for the details on each step.

  1. Setup a  LAMP server (Linux, Apache, MySQL, PHP server)
  2. Setup a database in MySQL
  3. Upload the Moodle package and install it

Setup a LAMP Server – Vanilla install of Ubuntu with Apache, MySQL, PHP

Start with the Ubuntu Install on a virtual machine

  • Start Installing Ubuntu Server and follow the prompts
  • Write down username and password and login
  • Install VMware Tools
    sudo apt-get install open-vm-tools
  • Install Apache, MySQL and PHP
    sudo apt-get install tasksel
    sudo tasksel install lamp-server
    Write down the username and password you choose for MySQL
  • Install SSH for remote administration
    sudo apt-get install openssh-server
  • Install and setup vsftpd (https://help.ubuntu.com/10.04/serverguide/ftp-server.html)
    sudo apt-get install vsftpd
    sudo nano /etc/vsftpd.conf
    • local_enable=YES
    • write_enable=YES

sudo /etc/init.d/vsftpd restart

  • Install text/image tools:
    • sudo apt-get install latex (use texlive instead)
    • sudo apt-get install ghostscript
    • sudo apt-get install convert
    • sudo apt-get install dvips
    • o    sudo apt-get install texlive-full
    • sudo apt-get install imagemagick
    • sudo apt-get install mimetex
    • Install various PHP extentions:
      • sudo apt-get install php5-ldap
      • sudo apt-get install php5-curl
      • sudo apt-get install php5-ldap
      • sudo apt-get install php5-gd
      • sudo apt-get install php5-intl
      • sudo apt-get install php5-xmlrpc
      • sudo /etc/init.d/apache2 restart
      • Optional Install Clam Antivirus: sudo apt-get install clamav
      • Optional Install Spell Checker: sudo apt-get install unzip zip aspell-en aspell-fr aspell-de aspell-es

Setup a Database in MySQL

You now need a database for moodle to save data to. You installed the MySQL service in the pervious steps but you might not have creaed a database for the data to be saved in yet. If this MySQL installations is only holding Moodle data then you can just use the same username and password for the moodle database as the MySQL administrator otherwise it is a good security idea to create seporate users for each database MySQL will be hosting. Please that once you login to MySQL all  statements will need to end with a ;

Login to MySQL (skip the second setp if only one database administor will be used)

  • Optional change the password (only needed if you didn’t set a good password during install)
    • mysqladmin -u root password yourownpassword
    • mysqladmin -u root -h localhost password yourownpassword
    • Optional create a separate user and create the permissions for that users for the moodle database using the using the set permissions script Type:  mysql_setpermission and follow the prompts to create a new users and assign them permissions on the moodle database.
    • Login to MySQL as the administrator:
    • mysql -u root –p
      type in the password
    • Create a database for Moodle to use: CREATE DATABASE moodle Default Character Set;
    • Logout of MySQL: exit;

 

Upload The Moodle Package and Install It

Moodle needs to folders:  “moodledata” and “moodle”
The “moodle” holds the actual moodle files that actually run moodle. This folder will need to be in a place accessible by the internet.
The “moodledata’ holds user files and temp files and needs read, write, execute permissions. This folder should not be accessible by the internet directly.
The “moodle” needs read and execute to run Moodle but during the install it will need write access as well because it creates a config.php file. You can create and set permissions in two ways, either via the command line in Ubuntu or via an FTP client program.

  • Option 1 – Use FTP to create and set permissions of the Moodle folders
    • FTP to the server and navigate to the web directory usually: /var
    • create the directory: /var/moodledata  and  /var/www/moodle
    • use your FTP client to change the permissions for the two folders to 777
    • Option 2 – Use Ubuntu command line to create and set permissions of the Moodle folders
      • mkdir -p /var/www/moodle
      • chmod -R 777 /var/www/moodle
      • mkdir -p /var/moodledata
      • chmod -R 777 /var/moodledata

Now you need to upload the moodle files and install Moodle.

  • Download the Moodle package from moodle.org and unzip/extract the package
  • Use your FTP client to upload the moodle package to your newly created moodle directory.
  • You should have already set up the domain name or IP address you will be using to access Moodle and it should point to your Ubuntu server. This will be the IP address or domain name everyone will be using to access Moodle.
    • http://192.168.1.10/moodle or  http://mydomain.com/moodle
    • It will ask you for the language of your choice. Choose EN for English
    • Now it will ask you for the installation settings of your server.
      • Data folder will be: /var/moodledata
      • Database Type: MySQL
      • Database will be: moodle
      • Database User: use the username you used for your MySQL database
      • Database Password: password you used for your MySQL database
      • Table Prefix can be anything as long as no other program will be using it in your database. If your database is only used for Moodle you can call it anything. I use mdl_
  • If the moodle directory has write access the installation will write the config.php file there using the settings you just chose. If not you will need to create the file by copying the code Moodle provides during the install and uploading it in a file called config.php to your moodle directory.
  • You should be good to go at this point. Just follow the prompts and set the settings what work for your environment. If you are unsure the default settings usually are a good start.

 

Other Useful Linux Ubuntu commands:

  • Reboot Server: sudo shutdown -r now
  • Shutdown Server: sudo shutdown -h now
  • To install a package: sudo apt-get install PackageName
  • To uninstall a package: sudo apt-get remove PackageName
  • To update package indes: sudo apt-get update
  • To update installed packages: sudo apt-get upgrade
  • To update Ubuntu to the latest distro: sudo apt-get dist-upgrade
  • To delete a directory and all subfolders and files: rm -rf DirectoryName
  • Make a copy of moodledata directory for a backup before updateing
    • mkdir moodledata_backup
    • cp -r /var/www/moodledata/* /var/www/moodledata_backup
  • Restart Apache: /etc/init.d/httpd restart
  •  Start vsftpd: /sbin/service vsftpd start
  •  Stop vsftpd: /sbin/service vsftpd stop
  • Restart vsftpd: /sbin/service vsftpd restart
  • MySQL Backup: mysqldump -u root -p -C -Q -e -a moodle > moodle-backup.sql
  • MySQL Restore: mysql -u root -p moodle < moodle-backup.sql

 

Posted in Computers & Technology, Uncategorized | Comments Off on Setup Moodle on Ubuntu Tutorial

Setup Moodle on Ubuntu Tutorial

I have done several Moodle installs on RedHat, CentOS, and Ubuntu Server. By far I like Ubuntu the most. Ubuntu Server is simple to setup and use, plus it is a much lighter weight server then the others and boots much faster. I also usually have fewer issues with setting up Moodle on Ubuntu. Don’t get me wrong RedHat and CentOS (which are the same thing) are great operating systems and are not difficult either, it is just hard to beat Ubuntu.

This tutorial may seem long and difficult but don’t get discouraged. It is a lot more simple then is seems.  I have included all the Ubuntu Server commands you should need.  At the end of this post I have included some Linux commands that I have found useful when using Ubuntu Server. A note about Ubuntu, often you need to run a command as root or administrator. This is knows as the super user and is accomplished by prefixing the command with sudo. Example: sudo apt-get install package

Here are the overall steps you need. Look below for the details on each step.

  1. Setup a  LAMP server (Linux, Apache, MySQL, PHP server)
  2. Setup a database in MySQL
  3. Upload the Moodle package and install it

Setup a LAMP Server – Vanilla install of Ubuntu with Apache, MySQL, PHP

Start with the Ubuntu Install on a virtual machine

  • Start Installing Ubuntu Server and follow the prompts
  • Write down username and password and login
  • Install VMware Tools
    sudo apt-get install open-vm-tools
  • Install Apache, MySQL and PHP
    sudo apt-get install tasksel
    sudo tasksel install lamp-server
    Write down the username and password you choose for MySQL
  • Install SSH for remote administration
    sudo apt-get install openssh-server
  • Install and setup vsftpd (https://help.ubuntu.com/10.04/serverguide/ftp-server.html)
    sudo apt-get install vsftpd
    sudo nano /etc/vsftpd.conf
    • local_enable=YES
    • write_enable=YES

sudo /etc/init.d/vsftpd restart

  • Install text/image tools:
    • sudo apt-get install latex (use texlive instead)
    • sudo apt-get install ghostscript
    • sudo apt-get install convert
    • sudo apt-get install dvips
    • o    sudo apt-get install texlive-full
    • sudo apt-get install imagemagick
    • sudo apt-get install mimetex
    • Install various PHP extentions:
      • sudo apt-get install php5-ldap
      • sudo apt-get install php5-curl
      • sudo apt-get install php5-ldap
      • sudo apt-get install php5-gd
      • sudo apt-get install php5-intl
      • sudo apt-get install php5-xmlrpc
      • sudo /etc/init.d/apache2 restart
      • Optional Install Clam Antivirus: sudo apt-get install clamav
      • Optional Install Spell Checker: sudo apt-get install unzip zip aspell-en aspell-fr aspell-de aspell-es

Setup a Database in MySQL

You now need a database for moodle to save data to. You installed the MySQL service in the pervious steps but you might not have creaed a database for the data to be saved in yet. If this MySQL installations is only holding Moodle data then you can just use the same username and password for the moodle database as the MySQL administrator otherwise it is a good security idea to create seporate users for each database MySQL will be hosting. Please that once you login to MySQL all  statements will need to end with a ;

Login to MySQL (skip the second setp if only one database administor will be used)

  • Optional change the password (only needed if you didn’t set a good password during install)
    • mysqladmin -u root password yourownpassword
    • mysqladmin -u root -h localhost password yourownpassword
    • Optional create a separate user and create the permissions for that users for the moodle database using the using the set permissions script Type:  mysql_setpermission and follow the prompts to create a new users and assign them permissions on the moodle database.
    • Login to MySQL as the administrator:
    • mysql -u root –p
      type in the password
    • Create a database for Moodle to use: CREATE DATABASE moodle Default Character Set;
    • Logout of MySQL: exit;

 

Upload The Moodle Package and Install It

Moodle needs to folders:  “moodledata” and “moodle”
The “moodle” holds the actual moodle files that actually run moodle. This folder will need to be in a place accessible by the internet.
The “moodledata’ holds user files and temp files and needs read, write, execute permissions. This folder should not be accessible by the internet directly.
The “moodle” needs read and execute to run Moodle but during the install it will need write access as well because it creates a config.php file. You can create and set permissions in two ways, either via the command line in Ubuntu or via an FTP client program.

  • Option 1 – Use FTP to create and set permissions of the Moodle folders
    • FTP to the server and navigate to the web directory usually: /var
    • create the directory: /var/moodledata  and  /var/www/moodle
    • use your FTP client to change the permissions for the two folders to 777
    • Option 2 – Use Ubuntu command line to create and set permissions of the Moodle folders
      • mkdir -p /var/www/moodle
      • chmod -R 777 /var/www/moodle
      • mkdir -p /var/moodledata
      • chmod -R 777 /var/moodledata

Now you need to upload the moodle files and install Moodle.

  • Download the Moodle package from moodle.org and unzip/extract the package
  • Use your FTP client to upload the moodle package to your newly created moodle directory.
  • You should have already set up the domain name or IP address you will be using to access Moodle and it should point to your Ubuntu server. This will be the IP address or domain name everyone will be using to access Moodle.
    • http://192.168.1.10/moodle or  http://mydomain.com/moodle
    • It will ask you for the language of your choice. Choose EN for English
    • Now it will ask you for the installation settings of your server.
      • Data folder will be: /var/moodledata
      • Database Type: MySQL
      • Database will be: moodle
      • Database User: use the username you used for your MySQL database
      • Database Password: password you used for your MySQL database
      • Table Prefix can be anything as long as no other program will be using it in your database. If your database is only used for Moodle you can call it anything. I use mdl_
  • If the moodle directory has write access the installation will write the config.php file there using the settings you just chose. If not you will need to create the file by copying the code Moodle provides during the install and uploading it in a file called config.php to your moodle directory.
  • You should be good to go at this point. Just follow the prompts and set the settings what work for your environment. If you are unsure the default settings usually are a good start.

 

Other Useful Linux Ubuntu commands:

  • Reboot Server: sudo shutdown -r now
  • Shutdown Server: sudo shutdown -h now
  • To install a package: sudo apt-get install PackageName
  • To uninstall a package: sudo apt-get remove PackageName
  • To update package indes: sudo apt-get update
  • To update installed packages: sudo apt-get upgrade
  • To update Ubuntu to the latest distro: sudo apt-get dist-upgrade
  • To delete a directory and all subfolders and files: rm -rf DirectoryName
  • Make a copy of moodledata directory for a backup before updateing
    • mkdir moodledata_backup
    • cp -r /var/www/moodledata/* /var/www/moodledata_backup
  • Restart Apache: /etc/init.d/httpd restart
  •  Start vsftpd: /sbin/service vsftpd start
  •  Stop vsftpd: /sbin/service vsftpd stop
  • Restart vsftpd: /sbin/service vsftpd restart
  • MySQL Backup: mysqldump -u root -p -C -Q -e -a moodle > moodle-backup.sql
  • MySQL Restore: mysql -u root -p moodle < moodle-backup.sql

 

Posted in Computers & Technology | Comments Off on Setup Moodle on Ubuntu Tutorial

Delete all tables with the same prefex from a MySQL Database

There have been times when I need to drop many tables in a MySQL database. Usually the table names start with similar names. Of example if had a Moodle installation you may have 200 table names starting with moodle_ or if you have WordPress you may have 150 tables starting with wp_. Now the time has come you want to delete Moodle or WordPress and don’t need all those tables in your MySQL database anymore but deleting them one at a time is too time consuming.  Here is how I do it and it takes about 2 minutes.

If you are working with a remote database save yourself some time and download and install MySQL Workbench on your computer.

For my tutorial I will used this information as my database and table:
Example Database Name is:    MyDataBase
Example Table Name is:         TBL_data1

If you wanted to delete this table manually you would use this MySQL statement:
drop table `MyDataBase`.`TBL_data1`;

Now you could type that in for every table but after the 150th you might get tired of it. Follow these steps to delete them all at the same time:

** Step 1 **
-&gt; login to your MySQL database. I used MSQL Workbench

** Step 2 **
-&gt; use this  MySQL Statement: Show TABLES;
this will print out all the tabls in the database like so:
TBL_data1
TBL_data2
TBL_data3
TBL_data4
TBL_data5

** Step 3 **
-&gt; now copy all the tables you wish to delete and past them into a text editor like notepad++
You will now have a list of tables like this in your text editor:
‘TBL_data1’
‘TBL_data2’
‘TBL_data3’
‘TBL_data4’
‘TBL_data5’

** Step 4 **
Add the beginning of the SQL statement
-&gt; use find and replace: find all ‘TBL_ and replace with: drop table `MyDataBase`.`TBL_
This will add the beginning of the SQL statement to each table so it looks like this:
drop table `MyDataBase`.`TBL_data1′
drop table `MyDataBase`.`TBL_data2′
drop table `MyDataBase`.`TBL_data3′
drop table `MyDataBase`.`TBL_data4′
drop table `MyDataBase`.`TBL_data5′

** Step 5 **
Now finsih the SQL statement by adding the `:
-&gt; use find and replace: Find all ‘ to a `;

** Step 6 **
Now copy and run all the statements you created.
-&gt; Past all the SQL statements into MySQL (again I am using MySQL Workbench)
-&gt; press ctrl+shift+enter to run all the statements
Done.

Posted in Computers & Technology, Uncategorized | Comments Off on Delete all tables with the same prefex from a MySQL Database