Uploaded image for project: 'IGB'
  1. IGB
  2. IGBF-1804

Migrate database from sqllite to mysql(rds) - AppStore

    Details

    • Type: New Feature
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:

      Attachments

        Activity

        Hide
        svallapu Sai Charan Reddy Vallapureddy (Inactive) added a comment -

        [~aloraine]

        Migration from sqlite3 to MySQL is successful. Now, testappstore.bioviz.org uses MySQL DB.

        Show
        svallapu Sai Charan Reddy Vallapureddy (Inactive) added a comment - [~aloraine] Migration from sqlite3 to MySQL is successful. Now, testappstore.bioviz.org uses MySQL DB.
        Hide
        svallapu Sai Charan Reddy Vallapureddy (Inactive) added a comment - - edited

        [~aloraine]
        Migrate Appstore from sqllite3 to MySQL(rds)

        Steps:
        1. Create a mysql database in Amazon RDS.

        2. Log in to the database. (Note: Install MySQL before this step)
        Log in Commands:
        mysql -h HOSTNAME -u USERNAME -p"PASSWORD"

        3. Create a dedicated user to Appstore application.
        Create commands:

        create user username@'%' identified by "password"; (any server)
        create user username@'101.%.%.%' identified by "password";
        create user username@'usildb' identified by "password"; (user can only open from usildb server)

        4. Create Database
        CREATE DATABASE myproject CHARACTER SET UTF8;

        5. Grant all privileges to application user
        GRANT ALL PRIVILEGES ON myproject.* TO myprojectuser@localhost;

        6. Save the privileges
        FLUSH PRIVILEGES;

        7. exit from the mysql shell.

        8. Configure settings.py in appstore/appstore.
        DATABASES = {
        'default':

        { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'myproject', 'USER': 'myprojectuser(applicationuser)', 'PASSWORD': 'password', 'HOST': 'rds-mysql-host', 'PORT': '3306', }

        }

        or

        DATABASES = {
        'default': {
        'ENGINE': 'django.db.backends.mysql',
        'OPTIONS':

        { 'read_default_file': '/etc/mysql/my.cnf', }

        ,
        }
        }

        /etc/mysql/my.cnf:
        [client]
        database = DB_NAME
        host = localhost
        user = DB_USER
        password = DB_PASSWORD
        default-character-set = utf8

        9. Install mysqlDB
        pip install wheel
        pip install mysqlclient

        Then, edit the _init_.py file in your project origin dir(the same as settings.py)

        import pymysql
        pymysql.install_as_MySQLdb()

        10. Remove migrations files from below locations.
        rm /var/www/vhosts/appstore/apps/migrations/0001_initial.py
        rm /var/www/vhosts/appstore/download/migrations/0001_initial.py
        rm /var/www/vhosts/appstore/submit_app/migrations/0001_initial.py

        11.load tables and load data
        python manage.py makemigrations
        python manage.py migrate
        python manage.py loaddata appsDB.json
        python manage.py rebuild_index

        12.Give write permissions to the newly created whoosh_index file
        sudo chown -R www-data:www-data /var/www/vhosts/appstore/appstore/whoosh_index

        13.Restart Apache
        sudo service apache2 restart

        Show
        svallapu Sai Charan Reddy Vallapureddy (Inactive) added a comment - - edited [~aloraine] Migrate Appstore from sqllite3 to MySQL(rds) Steps: 1. Create a mysql database in Amazon RDS. 2. Log in to the database. (Note: Install MySQL before this step) Log in Commands: mysql -h HOSTNAME -u USERNAME -p"PASSWORD" 3. Create a dedicated user to Appstore application. Create commands: create user username@'%' identified by "password"; (any server) create user username@'101.%.%.%' identified by "password"; create user username@'usildb' identified by "password"; (user can only open from usildb server) 4. Create Database CREATE DATABASE myproject CHARACTER SET UTF8; 5. Grant all privileges to application user GRANT ALL PRIVILEGES ON myproject.* TO myprojectuser@localhost; 6. Save the privileges FLUSH PRIVILEGES; 7. exit from the mysql shell. 8. Configure settings.py in appstore/appstore. DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'NAME': 'myproject', 'USER': 'myprojectuser(applicationuser)', 'PASSWORD': 'password', 'HOST': 'rds-mysql-host', 'PORT': '3306', } } or DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'OPTIONS': { 'read_default_file': '/etc/mysql/my.cnf', } , } } /etc/mysql/my.cnf: [client] database = DB_NAME host = localhost user = DB_USER password = DB_PASSWORD default-character-set = utf8 9. Install mysqlDB pip install wheel pip install mysqlclient Then, edit the _ init _.py file in your project origin dir(the same as settings.py) import pymysql pymysql.install_as_MySQLdb() 10. Remove migrations files from below locations. rm /var/www/vhosts/appstore/apps/migrations/0001_initial.py rm /var/www/vhosts/appstore/download/migrations/0001_initial.py rm /var/www/vhosts/appstore/submit_app/migrations/0001_initial.py 11.load tables and load data python manage.py makemigrations python manage.py migrate python manage.py loaddata appsDB.json python manage.py rebuild_index 12.Give write permissions to the newly created whoosh_index file sudo chown -R www-data:www-data /var/www/vhosts/appstore/appstore/whoosh_index 13.Restart Apache sudo service apache2 restart

          People

          • Assignee:
            svallapu Sai Charan Reddy Vallapureddy (Inactive)
            Reporter:
            svallapu Sai Charan Reddy Vallapureddy (Inactive)
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: