How to update the database schema without losing existing data

Question

I have a custom module with my_module.install file defining my two data tables in mysql. Module is already installed and I am able to save data from forms into them. Now my requirements changed and I need to create a third table in the same module. How do I approach the reinstall of the module.

1) Prepare MYSQl scripts to backup the existing data into temp tables and after uninstall and reinstalling the module, copy back the data into tables from temp tables.

2) Looked into https://www.drupal.org/project/backup_migrate not compatible to drupal core version I am using 8.6.0

3) Any other modules or ideas?

edited after the suggestion: I have created the below function in mymodule.install file and also edited mymodule_schema() method and run drush updatedb. Still no db changes as expected table is not getting created. Not able to look into system table to get the recent version number as mentioned elsewhere because that table is not available in my current local DB.

   function mymodule_update_8601(){
    $spec = array(
    'fields' => array(
     'application_setup_id' => array(
     'type' => 'serial',
     'not null' => TRUE,
     'description' => 'Primary Key: ID',
     ),
    ),
   );
   $schema = Database::getConnection()->schema();
   $schema->createTable('application_setup', $spec);
  }

Answer 1

When you want to apply database schema changes you don't reinstall the module. You apply these changes via hook_update_N() from your MYMODULE.install file.

See Updating Database Schema and/or Data in Drupal 8 on drupal.org.

If your module is making a data model change related to database schema that your module defines with hook_schema(), then you need to properly update your data model. The two steps are:

1. Update your hook_schema() code so that it reflects your new data model if the database table and field definitions have changed. This will make sure that people that install your module after you made the change will install the correct database tables. See the Schema API documentation for details on that.
2. Write a hook_update_N() function. This will update the database for existing users of your module who already had it installed before you made the change so that they can continue to function. This is described below.

There also is a sample on how to add a new table from hook_update_N():

$spec = [
  'description' => 'My description',
  'fields'      => [
    'myfield1' => [
      'description' => 'Myfield1 description.',
      'type'        => 'varchar',
      'length'      => 255,
      'not null'    => TRUE,
      'default'     => '',
    ],
    'myfield2' => [
      'description' => 'Myfield2 description',
      'type'        => 'text',
      'not null'    => TRUE,
    ],
  ],
  'primary key' => ['myfield1'],
];
$schema = Database::getConnection()->schema();
$schema->createTable('mytable2', $spec);